언어64 postgreSQL select weather.id Idfrom Weather join Weather w -- on DATEDIFF(weather.recordDate, w.recordDate) = 1 --mysqlon weather.recordDate - w.recordDate = 1and weather.temperature>w.temperature select s.machine_id,round( cast( avg(e.timestamp - s.timestamp) as decimal), 3) processing_timefrom Activity s join Activity eon s.activity_type='start' and e.activity_type='end'and s.. 2024. 11. 30. union, union all SELECT product_id, FIRST_VALUE(new_price) OVER(PARTITION BY product_id ORDER BY change_date DESC) AS priceFROM ProductsWHERE change_date - 8월 16일 까지는 최근 가격- 8월 16일 안으로 갱신된 가격이 없으면 10달러- 중복 제거를 위해 union SELECT product_id, new_price AS priceFROM ProductsWHERE (product_id, change_date) IN (SELECT product_id,MAX(change_date) FROM Products .. 2024. 7. 26. lead with cte as(select id, num,lead(id,1) over() id1,lead(num,1) over() num1,lead(id,2) over() id2,lead(num,2) over() num2from logsorder by id)select distinct num 'ConsecutiveNums'from ctewhere num=num1 and num=num2 and id+1=id1 and id+2=id2 2024. 7. 26. 필터링 후 리턴 없을 때 null 출력하기 SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num) = 1 ORDER BY num DESC LIMIT 1위와 같은 방법으로는 having으로 필터링 후 도출되는 값이 없을 때, null이 아닌 빈 테이블이 출력됨 이럴 때, 테이블을 감싼 후 select로 다시 출력하면 빈테이블이 null 값으로 출력됨SELECT (SELECT num FROM MyNumbers GROUP BY num HAVING COUNT(num) = 1 ORDER BY num DESC LIMIT 1) AS num 가장 빠른 결과With cte AS(SELECT numFROM MyNumbers GROUP BY numHAVING COUNT(num)=1)SELECT MAX(num) as .. 2024. 7. 22. 누계 거리 순 select ROUTE,concat(round(sum(D_BETWEEN_DIST),1),'km') 'TOTAL_DISTANCE',concat(round(avg(D_BETWEEN_DIST),2), 'km') 'AVERAGE_DISTANCE'from SUBWAY_DISTANCEgroup by ROUTEorder by sum(D_BETWEEN_DIST) desc 출력할 때 문자열로 바뀌므로, 순서정렬에서 계산을 다시 해줌 2024. 7. 18. case when -> having select distinctcasewhen ((SKILL_CODE & (select sum(CODE) from SKILLCODES where CATEGORY = 'Front End')) and (SKILL_CODE & (select CODE from SKILLCODES where NAME = 'Python'))) then 'A'when SKILL_CODE & (select CODE from SKILLCODES where NAME = 'C#') then 'B'when SKILL_CODE & (select sum(CODE) from SKILLCODES where CATEGORY = 'Front End') then 'C'else nullend as 'grade',id,emailfrom S.. 2024. 7. 15. 중복 제거 select distinct ID,EMAIL,FIRST_NAME,LAST_NAMEfrom DEVELOPERS djoin SKILLCODES son d.SKILL_CODE & s.CODE > 0where CATEGORY='Front End'order by id 개발자가 여러 front end 기술을 가지면 여러 번 출력됨 -> 한 번만 출력해도 되므로 중복 제거 2024. 7. 12. format 소수 n번째 자리까지 select concat(format(max(length),2),'cm') 'MAX_LENGTH'from fish_info 2024. 7. 11. 데이터 조합 형식 select id, FISH_NAME, LENGTHfrom fish_info ijoin FISH_NAME_INFO non i.FISH_TYPE=n.FISH_TYPEwhere (i.fish_type, length) in (select fish_type, max(ifnull(length, 10)) from fish_info group by fish_type) 2024. 7. 10. union all with recursive tree as (select id, parent_id,1 as 'level'from ecoli_datawhere parent_id is nullunion select e1.id, e1.parent_id, e2.level + 1from ecoli_data e1join tree e2on e2.id=e1.parent_id -- 크로스 조인)select idfrom treewhere level=3order by id union해줄 때 테이블 구조 일치시키기! 2024. 7. 8. 이전 1 2 3 4 ··· 7 다음