SQL 学习技巧

1、表格自联结，内存大，带不懂，可以用窗口函数

,LEAD(a,b,c) over(partition by xxx  order by xxx ) --用于统计往下第b行a值，若无用c代替
,LAG (a,b,c) over(partition by xxx  order by xxx )  --用于统计往上第b行a值，若无用c代替

例如：lead() over(partition by order by )与lag() over(partition by order by )的用法_u011250186的博客-CSDN博客

SELECT c.brxh

,c.bah

,c.RYRQ

,c.CYRQ

,lead(ryrq,1) OVER(PARTITION BY bah ORDER BY ryrq) lead_ryrq

,(lead(ryrq,1) OVER(PARTITION BY bah ORDER BY ryrq)-cyrq) 再入院天数

,c.zryjh

,c.*

FROM bsl.T_BA_BRJBXX_BSL c

WHERE bah = '603721'

2、hex() 去重公式 （a,b）(b,a) 相同时，去重

Hive SQL去重a,b和b,a类型 - JavaShuo

mysql> SELECT t1.airport

,hex(t1.airport)

,t2.airport

,hex(t2.airport)

FROM t t1

,t t2

WHERE t1.airport != t2.airport

AND abs(t1.distant - t2.distant) < 100

;

2、grouping 的用法

(10条消息) GROUPING 用法_weixin_34270606的博客-CSDN博客

grouping(姓名) =1 表示 姓名列 为null 的取值

grouping(姓名)=0 表示 姓名列 为非null 的取值

grouping_id GROUPING_ID (Transact-SQL) - SQL Server | Microsoft Docs

可以用grouping_id 倒推出是否被组合：SQL函数之GROUPING_ID的使用 - 简书 (jianshu.com)

3、时间格式：

每个月第一天：

dateadd(order_pay_time,-day(order_pay_time)+1,'dd')

4、窗口函数  累计去重（user_id）

WITH CTE AS

(

SELECT biz_date,biz_date_month,user_id from

(

select

USER_ID

,TO_CHAR(order_pay_time,'yyyy-mm-dd') biz_date

,TO_CHAR(order_pay_time,'yyyy-mm') biz_date_month

,ROW_NUMBER() OVER(PARTITION BY user_id,TO_CHAR(order_pay_time,'yyyy-mm') ORDER BY TO_CHAR(order_pay_time,'yyyy-mm-dd')) AS UN

FROM aa

WHERE pt = ${date}

AND HOUR = ${hour}

AND sale_store_id = 676

)where un=1

)

select /*+MAPJOIN(b)*/c.biz_date,biz_date_month, COUNT(0) from cte b

inner join (select distinct biz_date from cte) c on c.biz_date >= b.biz_date

group by c.biz_date,biz_date_month;

5、窗口函数 累计求和

select

biz_date,biz_date_month

,SUM(order_sub_no) OVER(PARTITION BY biz_date_month order by biz_date )订单

from

(

SELECT TO_CHAR(order_pay_time,'yyyy-mm-dd') biz_date

,TO_CHAR(order_pay_time,'yyyy-mm') biz_date_month

,COUNT(order_sub_no) order_sub_no

FROM  aa 

WHERE pt = ${date}

AND HOUR = ${hour}

AND sale_store_id = 676

GROUP BY

TO_CHAR(order_pay_time,'yyyy-mm-dd')

,TO_CHAR(order_pay_time,'yyyy-mm')

)

GROUP BY biz_date,biz_date_month,order_pay_money

;