PostgreSQL/Codility SqlEventsDelta

[PostgreSQL/Codility] SqlEventsDelta

๐Ÿ“Œ๋ฌธ์ œ๋งํฌ

event_type์ด 2ํšŒ ์ด์ƒ์ธ ๊ฒฝ์šฐ, ๊ฐ€์žฅ ์ตœ๊ทผ ๋‚ ์งœ value - ๊ทธ ๋‹ค์Œ ๋‚ ์˜ value ์ฐจ์ด๋ฅผ ๊ตฌํ•˜๋Š” ๋ฌธ์ œ๋‹ค.

Codility ๋Š” Oracle๋ฅผ ์ง€์›ํ•˜์ง€ ์•Š๋Š”๋‹ค;;
์กฐ๊ธˆ ๋‹นํ™ฉํ–ˆ๋‹ค.. SQLite ์˜ ๊ฒฝ์šฐ row_number()๋ฅผ ์ง€์›ํ•˜์ง€ ์•Š์•„์„œ PostgreSQL๋กœ ํ’€์—ˆ๋‹ค.


solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select  event_type, sum(prev)-sum(next)
from    (select B.event_type , 
        case 
        when B.RN = 1 then value
        end as prev,
        case 
        when B.RN = 2 then value
        end as next
        from (select A.event_type, A.value, A.time, row_number() over(partition by A.event_type order by A.time desc )as RN
            from (select *
                from events
                where event_type in (
                    select distinct event_type
                    from events
                    group by event_type
                    having count(*)>=2) -- event_type 2๊ฐœ ์ด์ƒ์ธ ๊ฒฝ์šฐ ์ถ”์ถœ
                ) A 
            ) B -- row_number ๋กœ evnet_type ๋ณ„ ๋ฒˆํ˜ธ ๋งค๊น€
        ) C -- event_type ๋ณ„ prev, next ์—ด ์ถ”๊ฐ€
group by event_type
order by event_type

๋‹ค๋ฅธ์‚ฌ๋žŒ ํ’€์ด LEAD ํ•จ์ˆ˜ ์ด์šฉ

LEAD๋ฅผ ์ด์šฉํ•˜๋ฉด ์ฟผ๋ฆฌ๊ฐ€ ํ•œ๊ฒฐ ๊ฐ„๋‹จํ•ด์ง„๋‹ค.

1
2
3
4
5
6
7
8
9
10
11
12
13
select  B.event_type, B.result as value
from    (select A.event_type, A.value - lead(A.value) over(partition by A.event_type order by A.event_type) as result
        from (select event_type, value, time, row_number() over(partition by event_type order by time desc) as RN
            from events
            where event_type in (
                select distinct event_type
                from events
                group by event_type
                having count(*) >=2) -- event_type 2๊ฐœ ์ด์ƒ์ธ ๊ฒฝ์šฐ ์ถ”์ถœ
            ) A --row_number ๋กœ evnet_type ๋ณ„ ๋ฒˆํ˜ธ ๋งค๊น€
        where A.RN <=2) B --lead๋กœ ๋‘ํ–‰๊ฐ„์˜ ๊ฐ’์ฐจ์ด ๊ตฌํ•จ , 1ํ–‰๊ณผ 2ํ–‰๋งŒ ํ•„์š”ํ•˜๋ฏ€๋กœ R.RN <= 2 ์กฐ๊ฑด์ ˆ ์ถ”๊ฐ€
where B.result is not null 
order by B.event_type