100个SQL小知识

最新推荐文章于 2022-01-13 22:53:25 发布

yiyiyi01

最新推荐文章于 2022-01-13 22:53:25 发布

阅读量645

点赞数

本文链接：https://blog.csdn.net/yiyiyi01/article/details/112846870

版权

@ 100个SQL小知识

千里之行，始于足下

1. distance 从 0 米到10000米， 20米一档分为 500档

floor(distance/200)*200 as distance

2. grouping sets

select business_mode, source, count(uid)
from order_base
group by business_mode, source
grouping sets(business_mode);
达到group by 全部纬度一次， business_mode一次 union这两个表
详情见

3. [面筋] 查询6月1日到30日的观看时长0-60s，1-3min，3-10min…5个组的观看人数

select count(distinct if(duration <= 60, uid, null)) as '0~60s'
from (select uid, sum(duration) as duration
		from t1
		where date between '2020-06-01' and '2020-06-30'
		group by uid) tt1

进阶版—

用窗口函数代替子查询?

4. [面筋]查询每个组grp排序第2的uid

窗口函数 dense_rank

dense_rank, 出现并列第一, 第二名依然会返回2; rank并列第一, 第二名会返回3.

5. 次周留存的解法

周日苦哈哈加班写留存报表固化的我，灵机一动，想到了一个次周留存的新解法～

留存新算法整理

好处：一个SQL计算，新老周留存+本周频次

select shop_id, new_user_week_mark
	, count( distinct if(if_come_last_week = 0, uid, null)) as last_week_user_cnt -- 上周有多少客人
	, count(distinct if(come_both_week = 2, uid, null)) as last_week_user_re -- 上周客人这周留存
	, sum(order_cnt) as order_cnt --- 本周单量/用于计算周频次
	, count(distinct if (come_both_week = 2 or if_last_week = 0 , uid, null)) as this_week_user_cnt
	--- 本周用户数 = 这周的新课客（上周没来，这周来了 if_come_last_week = 1 ) + 上周的回头客 （come_both_week = 1) 

from (select shop_id, uid
		, min(week_num)  as if_come_last_week
		 -- 可以用来算上周客人的基数: 一个用户最小的week_num为0代表他上周有来； 为1代表上周没来
		, count(distinct week_num) as come_both_week  
		-- 是否两周都来了（即为上周客人这周留存）：一个用户具有两个week_num
		, count(distinct if(week_num = 1, order_id, null) ）as order_cnt --- 计算本周频次用 
from （select shop_id, uid ， order_id
,if(complete_time between '2021-03-01' and '2021-03-07', 0, 1) as week_num --- 0是上周， 1是本周
from order） tt0
group by shop_id, uid ) t0
		left join (
select uid, case when first_complete_time between '2021-03-01' and '2021-03-07' then ' last' 
			 when first_complete_time between '2021-03-08' and '2021-03-14' then 'this' 
			else 'else' end as new_user_week_mark
from user_info 
			) t1
group by shop_id, new_user_week_mark

6. grouping sets

同时得到a ,b ,c 维度下去重的uid_cnt,和 a，b维度下的uid_cnt

select  a, b,c, count(distinct uid) as uid_cnt
from t0
where concat_ws('-',year,month,day) between '2021-03-29' and '2021-03-30'
group by a, b,c
grouping sets ( (a, b), (a, b,c)) -- 指示按照这两种维度分别聚合一次

7. grouping sets

找到各种维度聚合的grouping——id

select stat_date, count(*)
,case when GROUPING__ID = 2 then 'total'
            when GROUPING__ID = 1 and category_name is null then 'other' else category_name end as category_name
            ,GROUPING__ID as grouping_id
from t1
group by ( (stat_date, category_name), stat_date)