SQL行转列（统计每天，每个用户的消费金额）及sql查询连续天数示例

SQL⾏转列（统计每天，每个⽤户的消费⾦额）及sql查询连续
天数⽰例
sql 脚本
创建⼀个订单统计表格，并插⼊数据
create table `tb_order` (
`order_id` int (11),
`user_id` int (11),
`gmv` float ,
`create_date` datetime
);
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('1','1','100.00','2017-10-01 15:44:18');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('2','1','200.00','2017-10-02 15:44:24');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('3','2','4321.00','2017-10-03 15:44:28');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('4','1','5678.00','2017-10-04 15:44:33');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('5','2','312.00','2017-10-05 15:44:50');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('6','2','134.00','2017-10-06 16:18:08');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('7','1','200.00','2017-10-03 15:44:24');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('8','1','200.00','2017-10-04 15:44:24');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('9','1','200.00','2017-10-05 15:44:24');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('10','1','200.00','2017-10-06 15:44:24');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('11','3','100.00','2017-10-01 15:44:18');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('12','4','4321.00','2017-10-03 15:44:28');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('13','3','5678.00','2017-10-04 15:44:33');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('14','4','312.00','2017-10-05 15:44:50');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('15','4','134.00','2017-10-06 16:18:08');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('16','3','200.00','2017-10-01 15:44:18');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('17','3','300.00','2017-10-02 15:44:24');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('18','3','3321.00','2017-10-03 15:44:28');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('19','3','4678.00','2017-10-04 15:44:33');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('20','3','212.00','2017-10-05 15:44:50');
insert into `tb_order` (`order_id`, `user_id`, `gmv`, `create_date`) values('21','3','634.00','2017-10-06 16:18:08');
解决问题
统计每个⽤户，每天的消费表(⾏转列适⽤）
SELECT user_id,
SUM(CASE WHEN `dd`=6 THEN gmv ELSE NULL END) AS `day6opt`,
SUM(CASE WHEN `dd`=5 THEN gmv ELSE NULL END) AS `day5opt`,
SUM(CASE WHEN `dd`=4 THEN gmv ELSE NULL END) AS `day4opt`,
SUM(CASE WHEN `dd`=3 THEN gmv ELSE NULL END) AS `day3opt`,
SUM(CASE WHEN `dd`=2 THEN gmv ELSE NULL END) AS `day2opt`,
SUM(CASE WHEN `dd`=1 THEN gmv ELSE NULL END) AS `day1opt`
FROM (
SELECT user_id,DAY(`create_date`) AS dd,gmv
FROM `tb_order`
)C
GROUP BY `user_id`
分组统计
SELECT user_id,
SUM(CASE WHEN `dd`=6 THEN gmv ELSE NULL END) AS `day6opt`,
SUM(CASE WHEN `dd`=5 THEN gmv ELSE NULL END) AS `day5opt`,
SUM(CASE WHEN `dd`=4 THEN gmv ELSE NULL END) AS `day4opt`,
SUM(CASE WHEN `dd`=3 THEN gmv ELSE NULL END) AS `day3opt`,
SUM(CASE WHEN `dd`=2 THEN gmv ELSE NULL END) AS `day2opt`,
SUM(CASE WHEN `dd`=1 THEN gmv ELSE NULL END) AS `day1opt`
FROM (
SELECT user_id,DAY(`create_date`) AS dd,gmv
FROM `tb_order`
)C
GROUP BY `user_id`
查找10⽉6号下单的⽤户以及他们的连续下单天数
# step1 查找10⽉6号下单的⽤户
SELECT user_id
FROM `tb_order`
WHERE DATE(`create_date`)='2017-10-06'
# step2 按⽤户进⾏分组，按⽇期排序
SELECT DISTINCT(DATE(create_date)) `DATE`,user_id,dense_rank() over (PARTITION BY user_id ORDER BY DATE(create_date) )`rank` FROM `tb_order`
# step3 ⽤⽇期 DATE 减去相应的排序号，得到 datediff
SELECT (`date`- `rank`)AS `datediff`
FROM (
SELECT DISTINCT(DATE(create_date)) `DATE`,user_id,dense_rank() over (PARTITION BY user_id ORDER BY DATE(create_date) )`rank` FROM `tb_order`
)S
# step4 获得10⽉6号下单的⽤户，他们的⽇期与序号差值
SELECT user_id,`DATE`,(`date`- `rank`)AS `datediff`
FROM (
SELECT DISTINCT(DATE(create_date)) `DATE`,user_id,dense_rank() over (PARTITION BY user_id ORDER BY DATE(create_date) )`rank` FROM `tb_order`
WHERE user_id IN
(
SELECT user_id
FROM `tb_order`
WHERE DATE(`create_date`)='2017-10-06'
)
)B
# step5 获得10⽉6号下单的⽤户，他们的连续下单天数
SELECT user_id,day_number,rn
FROM
(
SELECT user_id,COUNT(1) AS day_number,row_number() over (PARTITION BY user_id)rn
FROM
(
SELECT user_id,`DATE`,(`date`- `rank`)AS `datediff`
FROM (
SELECT DISTINCT(DATE(create_date)) `DATE`,user_id,dense_rank() over (PARTITION BY user_id ORDER BY DATE(create_date) )`rank` FROM `tb_order`
WHERE user_id IN
(
SELECT user_id
FROM `tb_order`
WHERE DATE(`create_date`)='2017-10-06'
)
)B
)C
GROUP BY user_id,`datediff`
)G。