[MySQL] 날짜별 순서 예제

*본 문제는 모 기업 코딩테스트에서 나온 문제를 제 방식대로 변형한 문제입니다.

---

문제. orders 테이블 안에 결제완료인 주문에 한해서 해당 멤버(member_id)의 몇 번째 주문(order_sequence)를 나타내는 쿼리를 작성하시오.

*최종결과에는 member_id, order_time, order_sequence만 나타내시오.

 

 

orders 스키마

* 해당 테이블은 임의로 값을 넣은 예시입니다.

스키마 작성 코드

* 해당 values는 연습용으로 만든 값입니다.

CREATE TABLE orders
    (`order_code` int, `order_time` datetime, `pay_money` int, `pay_yn` varchar(1), `member_id` int)
;
    
INSERT INTO orders
    (`order_code`, `order_time`, `pay_money`, `pay_yn`, `member_id`)
VALUES
    (387836, '2018-04-13 10:20:57', 4500, 'y', 70955),
    (345477, '2017-12-16 03:30:43', 34200, 'y', 70955),
    (531013, '2019-05-16 19:22:28', 56300, 'y', 70955),
    (593478, '2019-11-07 01:32:34', 4500, 'y', 218095),
    (253089, '2017-04-03 04:38:06', 21400, 'y', 218095),
    (418074, '2018-07-07 03:24:23', 22250, 'y', 192368),
    (57114, '2015-10-06 13:03:50', 56300, 'y', 192368),
    (176732, '2016-09-02 06:57:25', 82520, 'y', 192368),
    (109484, '2016-02-28 20:31:45', 13150, 'y', 192368),
    (412987, '2018-06-22 20:44:12', 53350, 'y', 192368),
    (639937, '2020-03-15 08:04:46', 57900, 'y', 302700),
    (163256, '2016-07-27 00:51:49', 28500, 'y', 216440),
    (490971, '2019-01-24 21:36:25', 24070, 'y', 205394),
    (570774, '2019-09-04 20:53:53', 63700, 'y', 205394),
    (440350, '2018-09-07 02:50:13', 71300, 'y', 258721),
    (9937, '2015-05-28 21:13:29', 14900, 'y', 108191),
    (199243, '2016-11-03 14:04:46', 14350, 'y', 409520),
    (132627, '2016-05-03 03:47:01', 42000, 'y', 161249),
    (296216, '2017-08-01 09:28:18', 61100, 'y', 161249),
    (498143, '2019-02-14 00:53:14', 46800, 'n', 219228),
    (648427, '2020-04-07 23:54:34', 76580, 'y', 145132),
    (251436, '2017-03-29 12:11:16', 71300, 'y', 145132),
    (394545, '2018-05-02 00:40:31', 105600, 'y', 364892),
    (19351, '2015-06-23 14:55:21', 104990, 'y', 364892),
    (66250, '2015-10-31 17:11:22', 45610, 'y', 364892),
    (93707, '2016-01-16 03:36:54', 84900, 'y', 49549),
    (586361, '2019-10-18 09:35:47', 23700, 'y', 49549),
    (649945, '2020-04-12 02:32:07', 22260, 'y', 49549),
    (396265, '2018-05-07 00:57:25', 90000, 'y', 49549),
    (171180, '2016-08-18 02:32:34', 65720, 'y', 49549),
    (500201, '2019-02-19 20:23:14', 38880, 'y', 49549),
    (317690, '2017-09-30 02:49:52', 29180, 'y', 49549),
    (333643, '2017-11-13 00:40:42', 63500, 'y', 85945),
    (425427, '2018-07-27 17:04:06', 12000, 'y', 85945),
    (251630, '2017-03-30 02:05:29', 22880, 'y', 85945),
    (21583, '2015-06-29 19:18:42', 47640, 'y', 85945),
    (636503, '2020-03-05 20:17:25', 4500, 'y', 85945),
    (414687, '2018-06-27 13:29:17', 25000, 'y', 85945),
    (402242, '2018-05-23 22:20:01', 25000, 'y', 85945),
    (569755, '2019-09-02 01:36:23', 36500, 'y', 85945),
    (231983, '2017-02-03 14:16:29', 41800, 'y', 85945),
    (569147, '2019-08-31 08:05:23', 31360, 'y', 34860),
    (259941, '2017-04-22 10:49:20', 7940, 'y', 34860),
    (355886, '2018-01-14 05:14:24', 46500, 'y', 201712),
    (143841, '2016-06-02 23:02:42', 44380, 'y', 173537),
    (44787, '2015-09-02 08:30:07', 62430, 'n', 173537),
    (293278, '2017-07-24 02:30:12', 34750, 'y', 173537),
    (589367, '2019-10-26 17:43:08', 115800, 'y', 173537),
    (291692, '2017-07-19 12:52:50', 36800, 'y', 11890),
    (3540, '2015-05-11 01:31:39', 59500, 'y', 11890),
    (319982, '2017-10-06 09:46:59', 21900, 'y', 11890),
    (55004, '2015-09-30 14:12:20', 64000, 'y', 11890),
    (183613, '2016-09-21 01:48:23', 51080, 'y', 11890),
    (625740, '2020-02-05 02:00:39', 20100, 'y', 11890),
    (296971, '2017-08-03 11:14:37', 66410, 'y', 11890),
    (281254, '2017-06-20 20:58:41', 113400, 'y', 11890),
    (540842, '2019-06-13 02:54:58', 42680, 'y', 11890),
    (413161, '2018-06-23 09:00:36', 69440, 'y', 11890),
    (190602, '2016-10-10 13:36:45', 138460, 'y', 11890),
    (5926, '2015-05-17 13:27:58', 37660, 'y', 285071),
    (481819, '2018-12-30 10:09:21', 91700, 'y', 240824),
    (47275, '2015-09-09 02:54:06', 165500, 'y', 240824),
    (673674, '2020-06-16 20:53:11', 88000, 'y', 240824),
    (284597, '2017-06-30 01:06:58', 30000, 'y', 240824),
    (473538, '2018-12-07 19:29:05', 26550, 'y', 240824),
    (380514, '2018-03-24 02:40:03', 76100, 'y', 337863),
    (318290, '2017-10-01 18:42:59', 64800, 'y', 337863),
    (307910, '2017-09-02 21:08:38', 4500, 'y', 48202),
    (121282, '2016-04-01 16:02:23', 21200, 'y', 48202),
    (268403, '2017-05-15 19:54:55', 55400, 'y', 268525),
    (331310, '2017-11-06 13:15:17', 83100, 'y', 268525),
    (590169, '2019-10-28 21:40:21', 42680, 'y', 268525),
    (413349, '2018-06-23 20:38:46', 26400, 'y', 98775),
    (137197, '2016-05-15 15:44:07', 27100, 'n', 98775),
    (636402, '2020-03-05 13:41:24', 47200, 'y', 98775),
    (497549, '2019-02-12 09:01:17', 33500, 'y', 98775),
    (447147, '2018-09-25 21:21:59', 14900, 'n', 66846),
    (230231, '2017-01-29 14:55:29', 22750, 'y', 179643),
    (361878, '2018-01-31 03:35:37', 49300, 'y', 179643),
    (129277, '2016-04-23 21:15:34', 20900, 'y', 179643),
    (196195, '2016-10-25 23:33:20', 20550, 'y', 179643)
;

---

나의 풀이 in MySQL

SELECT member_id, order_time, order_sequence
FROM (
  SELECT A.*,
  (CASE @m WHEN A.member_id THEN @rownum := @rownum+1 ELSE @rownum := 1 END) order_sequence,
  (@m :=A.member_id) M
  FROM orders A, (SELECT @m:='', @rownum :=0 FROM DUAL) B
  ORDER BY A.member_id, A.order_time
) C;

 

풀이 설명

1. 서브쿼리를 이용해 orders 테이블에서 CASE WHEN으로 member_id 별 order_sequence를 만들어줍니다.

2. 듀얼 테이블을 활용해 @m의 변수에 member_id, @rownum 변수에 0값을 넣어줍니다.

3. member_id 별로 order해주고 날짜의 오름차순에 따른 order_sequence를 설정해줍니다.

 

코드 결과

* 해당 문제는 제가 여러 SQL 관련 코딩테스트를 치르며 문제와 테이블 모두 만든 문제입니다. 저처럼 SQL 관련 코딩테스트를 준비하시는 사람들에게 조금의 도움이라도 될까 생각하며 만들었습니다.

 

다른 문제들 보러가기

• SQL 코딩테스트 연습 예제 1
• SQL 코딩테스트 연습 예제 2