데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

MS-SQL Q&A 5863 게시물 읽기

No. 5863

일자별 최대값은 어떻게 구해야 할까요?
작성자	ㅊㅂ	작성일	2011-03-30 16:40ⓒ 2011-03-31 11:21ⓜ	조회수	7,586

날짜 | 값

2011-03-01 00:00:00 100

...
2011-03-01 10:10:10    100
2011-03-01 10:10:11   101
2011-03-01 10:10:12   102
2011-03-01 10:10:13   103
2011-03-01 10:10:14   104
2011-03-01 10:10:15   15
2011-03-01 10:10:16   16
2011-03-01 10:10:17   17
2011-03-01 10:10:18   18
2011-03-01 10:10:19   12
2011-03-01 10:10:20   155
2011-03-01 10:10:21   111
2011-03-01 10:10:22   112
2011-03-01 10:10:23   113
...
2011-03-02 10:10:23    14
2011-03-02 10:10:24    15
2011-03-02 10:10:25    16
2011-03-02 10:10:26    17
2011-03-02 10:10:27    18
2011-03-02 10:10:28    19
2011-03-02 10:10:29    0
2011-03-02 10:10:30    1
2011-03-02 10:10:31    2
2011-03-02 10:10:32    3
2011-03-02 10:10:33    4
2011-03-02 10:10:34    25
...
2011-03-03 10:10:35   125
2011-03-03 10:10:36   126
2011-03-03 10:10:37   127
2011-03-03 10:10:38   128
2011-03-03 10:10:39   129
2011-03-03 10:10:40   130
2011-03-03 10:10:41   131
2011-03-03 10:10:42   132
2011-03-03 10:10:43   133
2011-03-03 10:10:44   134
....
2011-03-30 23:59:59    250

~~위와 같은 데이터에서 각 날짜별 최대값을 구하고 싶습니다.~~

어떤식으로 구해야될까요?

--- 추가 ---

답변주신 착한넘님 감사합니다.

죄송하지만 제가 질문을 잘못한것 같네요.

값은 순차적으로 증가만 하지는 않는데 제가 예제의 값을 순차적 증가하도록 올려버렸네요.

각 날짜별 마지막 입력된 값을 가져오려고 했던 겁니다.

예제의 데이터로만 보면

2011-03-01 10:10:23 113

2011-03-02 10:10:34 25

2011-03-03 10:10:44 134

2011-03-30 23:59:59 250

이렇게 데이터를 가져오고 싶습니다.

이 글에 대한 댓글이 총 3건 있습니다.

WITH TMP as(
SELECT '2011-03-01 00:00:00' 날짜,100 값
UNION ALL select '2011-03-01 10:10:10',100
UNION ALL select '2011-03-01 10:10:11',101
UNION ALL select '2011-03-02 10:10:33',24
UNION ALL select '2011-03-02 10:10:34',25
UNION ALL select '2011-03-03 10:10:43',133
UNION ALL select '2011-03-03 10:10:44',134
UNION ALL select '2011-03-30 23:59:59', 250
)

SELECT SUBSTRING(날짜,1,10), MAX(값)
FROM tmp
GROUP BY SUBSTRING(날짜,1,10)

/*날짜가 DateTime이면*/

SELECT CONVERT(VARCHAR, Date, 112), max(값)
FROM tmp
GROUP BY CONVERT(VARCHAR, Date, 112)

착한넘(agoodman99)님이 2011-03-30 17:15에 작성한 댓글입니다.

WITH TB AS (
SELECT '2011-03-01 10:10:10' DATE1, '100' VALUES1
UNION ALL SELECT'2011-03-01 10:10:11',    '101'
UNION ALL SELECT'2011-03-01 10:10:12',    '102'
UNION ALL SELECT'2011-03-01 10:10:13',    '103'
UNION ALL SELECT'2011-03-01 10:10:14',    '104'
UNION ALL SELECT'2011-03-01 10:10:15',    '105'
UNION ALL SELECT'2011-03-01 10:10:16',    '106'
UNION ALL SELECT'2011-03-01 10:10:17',    '107'
UNION ALL SELECT'2011-03-01 10:10:18',    '198'
UNION ALL SELECT'2011-03-01 10:10:19',    '109'
UNION ALL SELECT'2011-03-01 10:10:20',    '110'
UNION ALL SELECT'2011-03-01 10:10:21',    '111'
UNION ALL SELECT'2011-03-01 10:10:22',    '112'
UNION ALL SELECT'2011-03-01 10:10:23',    '123'
UNION ALL SELECT'2011-03-02 10:10:23',    '14'
UNION ALL SELECT'2011-03-02 10:10:24',    '15'
UNION ALL SELECT'2011-03-02 10:10:25',    '16'
UNION ALL SELECT'2011-03-02 10:10:26',    '54'
UNION ALL SELECT'2011-03-02 10:10:27',    '18'
UNION ALL SELECT'2011-03-02 10:10:28',    '19'
UNION ALL SELECT'2011-03-02 10:10:29',    '20'
UNION ALL SELECT'2011-03-02 10:10:30',    '21'
UNION ALL SELECT'2011-03-02 10:10:31',    '22'
UNION ALL SELECT'2011-03-02 10:10:32',    '23'
UNION ALL SELECT'2011-03-02 10:10:33',    '24'
UNION ALL SELECT'2011-03-02 10:10:34',    '25'
)
SELECT DateMax, Values1
FROM TB a
RIGHT JOIN
  (SELECT MAX(DATE1) AS DateMax
    FROM TB
  GROUP BY SUBSTRING(DATE1, 1, 10))b on b.DateMax = a.Date1

가져다가 붙여넣어보세요. 제가 값에 있는 값은 조금 변경했습니다.

쫑님이 2011-03-31 14:51에 작성한 댓글입니다. Edit Ｘ

with t as (
     select '2011-03-01 00:00:00' dt, 100 val union all
     select '2011-03-01 10:10:10'   , 100     union all
     select '2011-03-01 10:10:11'   , 101     union all
     select '2011-03-01 10:10:12'   , 102     union all
     select '2011-03-01 10:10:13'   , 103     union all
     select '2011-03-01 10:10:14'   , 104     union all
     select '2011-03-01 10:10:15'   , 15      union all
     select '2011-03-01 10:10:16'   , 16      union all
     select '2011-03-01 10:10:17'   , 17      union all
     select '2011-03-01 10:10:18'   , 18      union all
     select '2011-03-01 10:10:19'   , 12      union all
     select '2011-03-01 10:10:20'   , 155     union all
     select '2011-03-01 10:10:21'   , 111     union all
     select '2011-03-01 10:10:22'   , 112     union all
     select '2011-03-01 10:10:23'   , 113     union all
     select '2011-03-02 10:10:23'   , 14      union all
     select '2011-03-02 10:10:24'   , 15      union all
     select '2011-03-02 10:10:25'   , 16      union all
     select '2011-03-02 10:10:26'   , 17      union all
     select '2011-03-02 10:10:27'   , 18      union all
     select '2011-03-02 10:10:28'   , 19      union all
     select '2011-03-02 10:10:29'   , 0       union all
     select '2011-03-02 10:10:30'   , 1       union all
     select '2011-03-02 10:10:31'   , 2       union all
     select '2011-03-02 10:10:32'   , 3       union all
     select '2011-03-02 10:10:33'   , 4       union all
     select '2011-03-02 10:10:34'   , 25      union all
     select '2011-03-03 10:10:35'   , 125     union all
     select '2011-03-03 10:10:36'   , 126     union all
     select '2011-03-03 10:10:37'   , 127     union all
     select '2011-03-03 10:10:38'   , 128     union all
     select '2011-03-03 10:10:39'   , 129     union all
     select '2011-03-03 10:10:40'   , 130     union all
     select '2011-03-03 10:10:41'   , 131     union all
     select '2011-03-03 10:10:42'   , 132     union all
     select '2011-03-03 10:10:43'   , 133     union all
     select '2011-03-03 10:10:44'   , 134     union all
     select '2011-03-30 23:59:59'   , 250
)
select dt
     , val
from (
         select substring(dt,1,10) dy
              , dt
              , val
              , row_number()over(partition by substring(dt,1,10) order by dt desc) rn
           from t
       ) a
where rn = 1

知音(sunnylee72)님이 2011-04-20 16:38에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
5866	쿼리문중 잘못된것좀 갈켜주세요... [1]	김우성	2011-04-02	6036
5865	MS-SQL 로그에는 어떤것들이 있나요?	김현우	2011-04-01	6454
5864	A에서 B로 데이터 복사하기 [1]	Antares	2011-03-31	6213
5863	일자별 최대값은 어떻게 구해야 할까요? [3]	ㅊㅂ	2011-03-30	7586
5862	재귀쿼리 삭제 문의	레이첼	2011-03-26	6356
5860	group by 질문입니다. [1]	쭌	2011-03-23	6215
5859	경우의수만큼 레코드를 나열하는 방법을 알고 싶어요^^	이만실	2011-03-22	7187