postgreSQL

select weather.id Id
from Weather join Weather w 
-- on DATEDIFF(weather.recordDate, w.recordDate) = 1 --mysql
on weather.recordDate - w.recordDate = 1
and weather.temperature>w.temperature

 

select s.machine_id,
round(
    cast(
            avg(e.timestamp - s.timestamp)
         as decimal),
        3) processing_time
from Activity s join Activity e
on s.activity_type='start' and e.activity_type='end'
and s.machine_id=e.machine_id and s.process_id=e.process_id
group by s.machine_id

-- 같은 작업

with
beginings as (select * from Activity where activity_type='start'),
endings as (select * from Activity where activity_type='end')

select machine_id,
round(
    avg(e.timestamp - s.timestamp)::decimal, -- cast -> ::
    3
) processing_time
from beginings s join
endings e
using(machine_id, process_id)   -- using 단독 사용 가능
group by machine_id

 

select name, bonus
from Employee e
left join Bonus b
using(empId)
where bonus < 1000 or bonus is null -- null 언급 필수

 

with cte as (
    select *
    from Students, Subjects
)

select student_id, student_name, subject_name,
count(e.subject_name) attended_exams -- * 또는 subject_name로 할 때는 null도 포함됨
from cte s
left join Examinations e
using(student_id, subject_name)
group by 1,2,3
order by 1,3

 

select s.name
from employee e
join employee s
on e.managerId=s.id
group by s.id, s.name
having count(*) >= 5

name만 출력해야하는데, 동명이인이 있으므로 group by로 id와 name을 같이 묶음

 

select user_id,
round(count(*) FILTER (where c.action='confirmed') / count(*)::numeric, 2) confirmation_rate
-- count(c.action='confirmed') 와 count(*) 결과 동일
-- filter 대신 CASE WHEN c.action = 'confirmed' THEN 1 END 가능
-- numeric으로 cast해줘야 소수로 계산
-- mysql: round(avg(if(action='confirmed', 1, 0)),2)
from signups s
left join confirmations c
using(user_id)
group by user_id

 

select p.product_id, -- prices 기준
coalesce(round(sum(units * price) / sum(units)::decimal,2),0) average_price -- mysql: ifnull
from unitssold u right join prices p -- unitssold를 기준으로 하면 (left) 팔리지 않은 상품에 대해 집계 불가
on u.product_id=p.product_id
and purchase_date between start_date and end_date
group by p.product_id -- prices 기준