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
;