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