HiveSQL练习题-count,sum开窗统计月度数据，求月用户新增

案例分析

已知一个表order_tab，有如下字段:
dt，order_id，user_id，amount。

数据样例:

2017-01-01	10029028	1000000001	33.57
2017-01-02	10029029	1000003252	52.10
2017-01-02	10029030	1000003253	87.39
2017-02-01	10029031	1000003254	65.20
2017-02-02	10029032	1000003255	41.80
2017-02-02	10029033	1000003255	48.80
2017-11-01	10029043	1000003266	34.20
2017-11-03	10029044	1000003267	55.80
2017-11-05	10029045	1000000001	72.90
2017-11-06	10029048	1000003268	92.90

建表语句:

CREATE TABLE order_tab (
  dt STRING,
  order_id STRING,
  user_id STRING,
  amount DECIMAL(10, 2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'

1. 给出2017年每个月的订单数、用户数、总成交金额。

 SELECT dt_month ,month_order,month_user
 ,MAX(sum_amount) 
 from (
	SELECT dt_month ,month_order,month_user
		,sum(amount) over (PARTITION by dt_month) as sum_amount
	from (
		SELECT dt_month,order_id ,user_id ,amount ,month_order
				,COUNT(DISTINCT user_id) OVER (PARTITION BY dt_month) AS month_user
		from (
			SELECT dt_month,order_id ,user_id ,amount
				,COUNT(*) OVER (PARTITION BY dt_month) AS month_order
			from (
				SELECT month(dt) as dt_month
					,order_id ,user_id ,amount 
				from order_tab
			)t1
		)t2
	)t3
)t4
GROUP BY dt_month,month_order ,month_user
ORDER BY dt_month;

2. 给出2017年11月的新客数(指在11月才有第一笔订单)

SELECT COUNT(DISTINCT user_id) AS new_count
FROM order_tab
WHERE month(dt) = 11
  AND user_id NOT IN (
    SELECT user_id
    FROM order_tab
    WHERE month(dt) < 11
  );

另一种解法

 select user_id,min(dt) from order_tab 
 group by user_id
 having min(dt) >= '2017-11-01'  and min(dt) <= '2017-11-30'