데이터베이스 질문과 답변
MSSQL을 사용하고 있습니다.
10분 마다 데이터를 테이블에 쌓고 있습니다.
| run_dt | accu |
| 2020-12-31 23:40:00 | 2 |
| 2020-12-31 23:50:00 | 7 |
| 2021-01-01 00:00:00 | 12 |
| 2021-01-01 00:10:00 | 27 |
| 2021-01-01 00:20:00 | 42 |
| 2021-01-01 00:30:00 | 52 |
| 2021-01-01 00:40:00 | 63 |
| 2021-01-01 00:50:00 | 68 |
| 2021-01-01 01:00:00 | 80 |
| 2021-01-01 01:10:00 | 90 |
| 2021-01-01 01:20:00 | 98 |
| 2021-01-01 01:30:00 | 110 |
| 2021-01-01 01:40:00 | 120 |
| 2021-01-01 01:50:00 | 125 |
| 2021-01-01 02:00:00 | 134 |
| 2021-01-01 02:10:00 | 145 |
| 2021-01-01 02:20:00 | 151 |
| 2021-01-01 02:30:00 | 164 |
| 2021-01-01 02:40:00 | 179 |
| 2021-01-01 02:50:00 | 191 |
| 2021-01-01 03:00:00 | 203 |
| 2021-01-01 03:10:00 | 215 |
| 2021-01-01 03:20:00 | 226 |
| 2021-01-01 03:30:00 | 233 |
| 2021-01-01 03:40:00 | 242 |
| 2021-01-01 03:50:00 | 250 |
| 2021-01-01 04:00:00 | 260 |
| 2021-01-01 04:10:00 | 272 |
| 2021-01-01 04:20:00 | 281 |
| 2021-01-01 04:30:00 | 292 |
| 2021-01-01 04:40:00 | 303 |
| 2021-01-01 04:50:00 | 313 |
| 2021-01-01 05:00:00 | 325 |
| 2021-01-01 05:10:00 | 334 |
| 2021-01-01 05:20:00 | 349 |
| 2021-01-01 05:30:00 | 362 |
| 2021-01-01 05:40:00 | 368 |
| 2021-01-01 05:50:00 | 379 |
| 2021-01-01 06:00:00 | 393 |
| 2021-01-01 06:10:00 | 405 |
| 2021-01-01 06:20:00 | 417 |
| 2021-01-01 06:30:00 | 424 |
| 2021-01-01 06:40:00 | 438 |
| 2021-01-01 06:50:00 | 451 |
| 2021-01-01 07:00:00 | 461 |
| 2021-01-01 07:10:00 | 476 |
| 2021-01-01 07:20:00 | 483 |
| 2021-01-01 07:30:00 | 497 |
| 2021-01-01 07:40:00 | 510 |
| 2021-01-01 07:50:00 | 525 |
| 2021-01-01 08:00:00 | 535 |
| 2021-01-01 08:10:00 | 541 |
| 2021-01-01 08:20:00 | 546 |
| 2021-01-01 08:30:00 | 557 |
| 2021-01-01 08:40:00 | 568 |
| 2021-01-01 08:50:00 | 574 |
| 2021-01-01 09:00:00 | 579 |
| 2021-01-01 09:10:00 | 589 |
| 2021-01-01 09:20:00 | 596 |
| 2021-01-01 09:30:00 | 601 |
| 2021-01-01 09:40:00 | 612 |
| 2021-01-01 09:50:00 | 627 |
| 2021-01-01 10:00:00 | 633 |
| 2021-01-01 10:10:00 | 646 |
| 2021-01-01 10:20:00 | 659 |
| 2021-01-01 10:30:00 | 674 |
| 2021-01-01 10:40:00 | 687 |
| 2021-01-01 10:50:00 | 694 |
| 2021-01-01 11:00:00 | 699 |
| 2021-01-01 11:10:00 | 706 |
| 2021-01-01 11:20:00 | 719 |
| 2021-01-01 11:30:00 | 732 |
| 2021-01-01 11:40:00 | 741 |
| 2021-01-01 11:50:00 | 753 |
| 2021-01-01 12:00:00 | 763 |
| 2021-01-01 12:10:00 | 773 |
| 2021-01-01 12:20:00 | 779 |
| 2021-01-01 12:30:00 | 784 |
| 2021-01-01 12:40:00 | 791 |
| 2021-01-01 12:50:00 | 803 |
| 2021-01-01 13:00:00 | 815 |
| 2021-01-01 13:10:00 | 828 |
| 2021-01-01 13:20:00 | 840 |
| 2021-01-01 13:30:00 | 853 |
| 2021-01-01 13:40:00 | 862 |
| 2021-01-01 13:50:00 | 872 |
| 2021-01-01 14:00:00 | 879 |
| 2021-01-01 14:10:00 | 893 |
| 2021-01-01 14:20:00 | 900 |
| 2021-01-01 14:30:00 | 907 |
| 2021-01-01 14:40:00 | 915 |
| 2021-01-01 14:50:00 | 927 |
| 2021-01-01 15:00:00 | 932 |
| 2021-01-01 15:10:00 | 946 |
| 2021-01-01 15:20:00 | 955 |
| 2021-01-01 15:30:00 | 963 |
| 2021-01-01 15:40:00 | 968 |
| 2021-01-01 15:50:00 | 973 |
| 2021-01-01 16:00:00 | 985 |
| 2021-01-01 16:10:00 | 999 |
| 2021-01-01 16:20:00 | 1011 |
| 2021-01-01 16:30:00 | 1024 |
| 2021-01-01 16:40:00 | 1034 |
| 2021-01-01 16:50:00 | 1047 |
| 2021-01-01 17:00:00 | 1054 |
| 2021-01-01 17:10:00 | 1066 |
| 2021-01-01 17:20:00 | 1075 |
| 2021-01-01 17:30:00 | 1087 |
| 2021-01-01 17:40:00 | 1101 |
| 2021-01-01 17:50:00 | 1112 |
| 2021-01-01 18:00:00 | 1119 |
| 2021-01-01 18:10:00 | 1129 |
| 2021-01-01 18:20:00 | 1139 |
| 2021-01-01 18:30:00 | 1152 |
| 2021-01-01 18:40:00 | 1161 |
| 2021-01-01 18:50:00 | 1173 |
| 2021-01-01 19:00:00 | 1182 |
| 2021-01-01 19:10:00 | 1194 |
| 2021-01-01 19:20:00 | 1201 |
| 2021-01-01 19:30:00 | 1209 |
| 2021-01-01 19:40:00 | 1219 |
| 2021-01-01 19:50:00 | 1229 |
| 2021-01-01 20:00:00 | 1236 |
| 2021-01-01 20:10:00 | 1251 |
| 2021-01-01 20:20:00 | 1260 |
| 2021-01-01 20:30:00 | 1273 |
| 2021-01-01 20:40:00 | 1288 |
| 2021-01-01 20:50:00 | 1296 |
| 2021-01-01 21:00:00 | 1308 |
| 2021-01-01 21:10:00 | 1316 |
| 2021-01-01 21:20:00 | 1326 |
| 2021-01-01 21:30:00 | 1337 |
| 2021-01-01 21:40:00 | 1352 |
| 2021-01-01 21:50:00 | 1358 |
| 2021-01-01 22:00:00 | 1363 |
| 2021-01-01 22:10:00 | 1373 |
| 2021-01-01 22:20:00 | 1383 |
| 2021-01-01 22:30:00 | 1391 |
| 2021-01-01 22:40:00 | 1405 |
| 2021-01-01 22:50:00 | 1411 |
| 2021-01-01 23:00:00 | 1424 |
| 2021-01-01 23:10:00 | 1434 |
| 2021-01-01 23:20:00 | 1441 |
| 2021-01-01 23:30:00 | 1452 |
| 2021-01-01 23:40:00 | 1457 |
| 2021-01-01 23:50:00 | 1467 |
| 2021-01-02 00:00:00 | 1475 |
| 2021-01-02 00:10:00 | 1480 |
| 2021-01-02 00:20:00 | 1489 |
| 2021-01-02 00:30:00 | 1499 |
| 2021-01-02 00:40:00 | 1508 |
| 2021-01-02 00:50:00 | 1513 |
| 2021-01-02 01:00:00 | 1522 |
| 2021-01-02 01:10:00 | 1531 |
| 2021-01-02 01:20:00 | 1544 |
| 2021-01-02 01:30:00 | 1549 |
| 2021-01-02 01:40:00 | 1563 |
| 2021-01-02 01:50:00 | 1571 |
| 2021-01-02 02:00:00 | 1581 |
| 2021-01-02 02:10:00 | 1595 |
| 2021-01-02 02:20:00 | 1609 |
| 2021-01-02 02:30:00 | 1620 |
| 2021-01-02 02:40:00 | 1630 |
| 2021-01-02 02:50:00 | 1643 |
| 2021-01-02 03:00:00 | 1656 |
| 2021-01-02 03:10:00 | 1671 |
| 2021-01-02 03:20:00 | 1686 |
| 2021-01-02 03:30:00 | 1694 |
| 2021-01-02 03:40:00 | 1705 |
| 2021-01-02 03:50:00 | 1710 |
| 2021-01-02 04:00:00 | 1719 |
| 2021-01-02 04:10:00 | 1734 |
| 2021-01-02 04:20:00 | 1749 |
| 2021-01-02 04:30:00 | 1755 |
| 2021-01-02 04:40:00 | 1766 |
| 2021-01-02 04:50:00 | 1781 |
| 2021-01-02 05:00:00 | 1791 |
| 2021-01-02 05:10:00 | 1796 |
| 2021-01-02 05:20:00 | 1804 |
| 2021-01-02 05:30:00 | 1816 |
| 2021-01-02 05:40:00 | 1825 |
| 2021-01-02 05:50:00 | 1839 |
| 2021-01-02 06:00:00 | 1848 |
| 2021-01-02 06:10:00 | 1861 |
| 2021-01-02 06:20:00 | 1870 |
| 2021-01-02 06:30:00 | 1875 |
| 2021-01-02 06:40:00 | 1882 |
| 2021-01-02 06:50:00 | 1893 |
| 2021-01-02 07:00:00 | 1899 |
| 2021-01-02 07:10:00 | 1905 |
| 2021-01-02 07:20:00 | 1912 |
| 2021-01-02 07:30:00 | 1926 |
| 2021-01-02 07:40:00 | 1939 |
| 2021-01-02 07:50:00 | 1953 |
| 2021-01-02 08:00:00 | 1961 |
| 2021-01-02 08:10:00 | 1969 |
| 2021-01-02 08:20:00 | 1977 |
| 2021-01-02 08:30:00 | 1987 |
| 2021-01-02 08:40:00 | 1999 |
| 2021-01-02 08:50:00 | 2004 |
| 2021-01-02 09:00:00 | 2012 |
| 2021-01-02 09:10:00 | 2021 |
| 2021-01-02 09:20:00 | 2029 |
| 2021-01-02 09:30:00 | 2039 |
| 2021-01-02 09:40:00 | 2044 |
| 2021-01-02 09:50:00 | 2052 |
| 2021-01-02 10:00:00 | 2067 |
| 2021-01-02 10:10:00 | 2076 |
| 2021-01-02 10:20:00 | 2084 |
| 2021-01-02 10:30:00 | 2093 |
| 2021-01-02 10:40:00 | 2108 |
| 2021-01-02 10:50:00 | 2121 |
| 2021-01-02 11:00:00 | 2130 |
| 2021-01-02 11:10:00 | 2141 |
| 2021-01-02 11:20:00 | 2152 |
| 2021-01-02 11:30:00 | 2157 |
| 2021-01-02 11:40:00 | 2164 |
| 2021-01-02 11:50:00 | 2173 |
| 2021-01-02 12:00:00 | 2182 |
| 2021-01-02 12:10:00 | 2197 |
| 2021-01-02 12:20:00 | 2205 |
| 2021-01-02 12:30:00 | 2214 |
| 2021-01-02 12:40:00 | 2225 |
| 2021-01-02 12:50:00 | 2234 |
| 2021-01-02 13:00:00 | 2242 |
| 2021-01-02 13:10:00 | 2254 |
| 2021-01-02 13:20:00 | 2260 |
| 2021-01-02 13:30:00 | 2272 |
| 2021-01-02 13:40:00 | 2278 |
| 2021-01-02 13:50:00 | 2287 |
| 2021-01-02 14:00:00 | 2293 |
| 2021-01-02 14:10:00 | 2299 |
| 2021-01-02 14:20:00 | 2313 |
| 2021-01-02 14:30:00 | 2318 |
| 2021-01-02 14:40:00 | 2329 |
| 2021-01-02 14:50:00 | 2334 |
| 2021-01-02 15:00:00 | 2341 |
| 2021-01-02 15:10:00 | 2348 |
| 2021-01-02 15:20:00 | 2362 |
| 2021-01-02 15:30:00 | 2375 |
| 2021-01-02 15:40:00 | 2380 |
| 2021-01-02 15:50:00 | 2390 |
| 2021-01-02 16:00:00 | 2401 |
| 2021-01-02 16:10:00 | 2413 |
| 2021-01-02 16:20:00 | 2426 |
| 2021-01-02 16:30:00 | 2434 |
| 2021-01-02 16:40:00 | 2442 |
| 2021-01-02 16:50:00 | 2449 |
| 2021-01-02 17:00:00 | 2464 |
| 2021-01-02 17:10:00 | 2477 |
| 2021-01-02 17:20:00 | 2485 |
| 2021-01-02 17:30:00 | 2499 |
| 2021-01-02 17:40:00 | 2511 |
| 2021-01-02 17:50:00 | 2525 |
| 2021-01-02 18:00:00 | 2533 |
| 2021-01-02 18:10:00 | 2542 |
| 2021-01-02 18:20:00 | 2557 |
| 2021-01-02 18:30:00 | 2566 |
| 2021-01-02 18:40:00 | 2575 |
| 2021-01-02 18:50:00 | 2583 |
| 2021-01-02 19:00:00 | 2597 |
| 2021-01-02 19:10:00 | 2603 |
| 2021-01-02 19:20:00 | 2613 |
위의 데이터를 아래와 같이 조회하고자 합니다.
00분에 해당하는 값을 구하고
최소와 최대에 해당하는 값은 그대로 표현하려고 합니다.
원하는 결과는 다음과 같습니다.
| run_dt | accu |
| 2020-12-31 23:40:00 | 2 |
| 2021-01-01 00:00:00 | 12 |
| 2021-01-01 01:00:00 | 80 |
| 2021-01-01 02:00:00 | 134 |
| 2021-01-01 03:00:00 | 203 |
| 2021-01-01 04:00:00 | 260 |
| 2021-01-01 05:00:00 | 325 |
| 2021-01-01 06:00:00 | 393 |
| 2021-01-01 07:00:00 | 461 |
| 2021-01-01 08:00:00 | 535 |
| 2021-01-01 09:00:00 | 579 |
| 2021-01-01 10:00:00 | 633 |
| 2021-01-01 11:00:00 | 699 |
| 2021-01-01 12:00:00 | 763 |
| 2021-01-01 13:00:00 | 815 |
| 2021-01-01 14:00:00 | 879 |
| 2021-01-01 15:00:00 | 932 |
| 2021-01-01 16:00:00 | 985 |
| 2021-01-01 17:00:00 | 1054 |
| 2021-01-01 18:00:00 | 1119 |
| 2021-01-01 19:00:00 | 1182 |
| 2021-01-01 20:00:00 | 1236 |
| 2021-01-01 21:00:00 | 1308 |
| 2021-01-01 22:00:00 | 1363 |
| 2021-01-01 23:00:00 | 1424 |
| 2021-01-02 00:00:00 | 1475 |
| 2021-01-02 01:00:00 | 1522 |
| 2021-01-02 02:00:00 | 1581 |
| 2021-01-02 03:00:00 | 1656 |
| 2021-01-02 04:00:00 | 1719 |
| 2021-01-02 05:00:00 | 1791 |
| 2021-01-02 06:00:00 | 1848 |
| 2021-01-02 07:00:00 | 1899 |
| 2021-01-02 08:00:00 | 1961 |
| 2021-01-02 09:00:00 | 2012 |
| 2021-01-02 10:00:00 | 2067 |
| 2021-01-02 11:00:00 | 2130 |
| 2021-01-02 12:00:00 | 2182 |
| 2021-01-02 13:00:00 | 2242 |
| 2021-01-02 14:00:00 | 2293 |
| 2021-01-02 15:00:00 | 2341 |
| 2021-01-02 16:00:00 | 2401 |
| 2021-01-02 17:00:00 | 2464 |
| 2021-01-02 18:00:00 | 2533 |
| 2021-01-02 19:00:00 | 2597 |
| 2021-01-02 19:20:00 | 2613 |
그리고, 얻은 결과로 다음과 같은 최종 결과를 얻고 싶습니다.
| 2020-12-31 23:00:00 | 2 |
| 2021-01-01 00:00:00 | 10 |
| 2021-01-01 01:00:00 | 68 |
| 2021-01-01 02:00:00 | 54 |
| 2021-01-01 03:00:00 | 69 |
| 2021-01-01 04:00:00 | 57 |
| 2021-01-01 05:00:00 | 65 |
| 2021-01-01 06:00:00 | 68 |
| 2021-01-01 07:00:00 | 68 |
| 2021-01-01 08:00:00 | 74 |
| 2021-01-01 09:00:00 | 44 |
| 2021-01-01 10:00:00 | 54 |
| 2021-01-01 11:00:00 | 66 |
| 2021-01-01 12:00:00 | 64 |
| 2021-01-01 13:00:00 | 52 |
| 2021-01-01 14:00:00 | 64 |
| 2021-01-01 15:00:00 | 53 |
| 2021-01-01 16:00:00 | 53 |
| 2021-01-01 17:00:00 | 69 |
| 2021-01-01 18:00:00 | 65 |
| 2021-01-01 19:00:00 | 63 |
| 2021-01-01 20:00:00 | 54 |
| 2021-01-01 21:00:00 | 72 |
| 2021-01-01 22:00:00 | 55 |
| 2021-01-01 23:00:00 | 61 |
| 2021-01-02 00:00:00 | 51 |
| 2021-01-02 01:00:00 | 47 |
| 2021-01-02 02:00:00 | 59 |
| 2021-01-02 03:00:00 | 75 |
| 2021-01-02 04:00:00 | 63 |
| 2021-01-02 05:00:00 | 72 |
| 2021-01-02 06:00:00 | 57 |
| 2021-01-02 07:00:00 | 51 |
| 2021-01-02 08:00:00 | 62 |
| 2021-01-02 09:00:00 | 51 |
| 2021-01-02 10:00:00 | 55 |
| 2021-01-02 11:00:00 | 63 |
| 2021-01-02 12:00:00 | 52 |
| 2021-01-02 13:00:00 | 60 |
| 2021-01-02 14:00:00 | 51 |
| 2021-01-02 15:00:00 | 48 |
| 2021-01-02 16:00:00 | 60 |
| 2021-01-02 17:00:00 | 63 |
| 2021-01-02 18:00:00 | 69 |
| 2021-01-02 19:00:00 | 64 |
| 2021-01-02 20:00:00 | 16 |
최종 결과는 뒷 시간과 앞 시간의 acuu 의 차이입니다.
이런 경우는 QUERY를 어떻게 작성해야 할까요?
질문이 부족하면 보충하도록 하겠습니다.
감사합니다.
by 마농
[2024.10.16 23:21:44]
SELECT run_dt
, accu
, accu - LAG(accu, 1, 0) OVER(ORDER BY run_dt) v
FROM (SELECT run_dt, accu
, MAX(run_dt) OVER() max_run_dt
, MIN(run_dt) OVER() min_run_dt
FROM t
) a
WHERE run_dt IN (max_run_dt, min_run_dt)
OR DATEPART(minute, run_dt) = 0
;
) 버튼을 클릭하여 작성 하시면 됩니다.