개발 공부 - SQL (SqlSum, SqlEventsDelta)

SqlSum

Given a table elements with the following structure:

create table elements ( v integer not null );

write an SQL query that returns the sum of the numbers in column v.

For example, given:

v --- 2 10 20 10

your query should return 42.

Copyright 2009–2023 by Codility Limited. All Rights Reserved. Unauthorized copying, publication or disclosure prohibited.

SELECT SUM(v) FROM ELEMENTS

SqlEventsDelta

Given a table events with the following structure:

create table events ( event_type integer not null, value integer not null, time timestamp not null, unique(event_type, time) );

write an SQL query that, for each event_type that has been registered more than once, returns the difference between the latest (i.e. the most recent in terms of time) and the second latest value. The table should be ordered by event_type (in ascending order).

For example, given the following data:

event_type | value | time ------------+------------+-------------------- 2 | 5 | 2015-05-09 12:42:00 4 | -42 | 2015-05-09 13:19:57 2 | 2 | 2015-05-09 14:48:30 2 | 7 | 2015-05-09 12:54:39 3 | 16 | 2015-05-09 13:19:57 3 | 20 | 2015-05-09 15:01:09

your query should return the following rowset:

event_type | value ------------+----------- 2 | -5 3 | 4

For the event_type 2, the latest value is 2 and the second latest value is 7, so the difference between them is −5.

The names of the columns in the rowset don't matter, but their order does.

Copyright 2009–2023 by Codility Limited. All Rights Reserved. Unauthorized copying, publication or disclosure prohibited.

WITH SORT_EVT AS(
    SELECT
        EVENT_TYPE,
        VALUE,
    TIME,
    RANK() OVER (PARTITION BY EVENT_TYPE ORDER BY TIME DESC) RANK
FROM
    EVENTS
    )

SELECT
    ONE.EVENT_TYPE AS event_type, ONE.VALUE-TWO.VALUE AS value
FROM
    (SELECT * FROM SORT_EVT WHERE RANK = 1) ONE
    JOIN
    (SELECT * FROM SORT_EVT WHERE RANK = 2) TWO
ON
    ONE.EVENT_TYPE = TWO.EVENT_TYPE
ORDER BY
    ONE.EVENT_TYPE

Oracle이 없다니!

그래도 CTE가 있어서 땡큐입니다.

요즘 오라클을 더 공부할지 / 마리아나 포스트그레로 갈 지 고민중인데

결론적으로는 오라클을 다시 리뷰 해볼까 하지만 실 사용은 JPA를 할 것 같아

튜닝 관련해서도 고오오민을 해보는 중입니다.

프론트 병합해서 많이 하다 보니까 리프레시 목적으로 복기를 하려는 중년.