常用sql场景

用户连续交易天数与连续交易额,与各自对应的日期区间

1.合并当天有多笔交易的用户的交易数据
2.如果有交易额为0的情况，过滤交易额为0的记录
3.按用户分组，以日期降序排序,相减后可将连续日期归为一组

ds	3.1	3.2	3.3	3.5	3.6	3.9
排序	1	2	3	4	5	6
相减	2.28	2.28	2.28	3.1	3.1	3.3

4.根据相减的值与用户id，分组计算

SELECT  user_id
        ,drn
        ,continual_ds
        ,continual_ds_rank
        ,continual_amt
        ,continual_amt_rank
        ,begin_dt
        ,end_dt
FROM    (
        SELECT  user_id
                ,drn
                ,COUNT(ds) AS continual_ds
                ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY COUNT(ds) DESC,drn DESC) AS continual_ds_rank 
                ,ROUND(SUM(pay_amt),2) AS continual_amt
                ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ROUND(SUM(pay_amt),2) DESC,drn DESC) AS continual_amt_rank 
                ,MIN(ds) AS begin_dt
                ,MAX(ds) AS end_dt
        FROM    (
                SELECT  user_id
                        ,ds
                        ,pay_amt
                        ,TO_CHAR(DATEADD(TO_DATE(ds,'yyyymmdd'),-ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY ds),'day'),'yyyymmdd') AS drn
                FROM    (
                        SELECT  user_id,ds,ROUND(SUM(pay_amt),2) AS pay_amt
                        FROM    lsk_cdm.dwd_lsk_trd_order_wide_di
                        WHERE   ds >= 20210201
                        and     ds <= 20210309
                        AND     is_valid_order = 1
                        GROUP BY user_id,ds
                ) t1
        ) t2
        GROUP BY user_id,drn
) t3
LIMIT 1000;

用户分平台交易笔数，金额统计

查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
思路：1.按用户日期分组，将平台拼接，金额求和
2.处理平台，将在两个平台交易的情况，转换为both

select t3.platform,spend_date,count(1) AS total_users,SUM(amount) AS total_amount
from   (
        --mysql中将collect_set替换为group_concat,并提前对platform去重
		select id,spend_date,concat_ws(',',COLLECT_SET(platform)) AS platform,SUM(amount) AS amount
		from   spending_table
		group by id,spend_date
) t2
left join (
		select 'mobile' AS pid,'mobile' AS platform union all 
		select 'desktop' AS pid,'desktop' AS platform union all 
		select 'mobile,desktop' AS pid,'both' AS platform union all 
		select 'desktop,mobile' AS pid,'both' AS platform
) t3
ON t2.platform = t3.pid

公司薪水中位数

公司员工的薪水中位数
Employee 表有三列：员工Id(主键)，公司名和薪水 【id,company,salary】
不使用任何内置的SQL函数的情况下,查找每个公司的薪水中位数(偶数返回2条，奇数返回1条)

1.full join获得同公司的薪水对比
2.以公司和薪水分组，求绝对值【求和（大于该薪水:+1,等于该薪水:+0,小于该薪水:-1）】，值为A
3.以公司和薪水分组，求该薪水的个数 B
4. B>=A,则该薪水为中位数

+-----+------------+--------+
|Id   | Company    | Salary |
+-----+------------+--------+
|1    | A          | 2341   |
|2    | A          | 341    |
|3    | A          | 15     |
|4    | A          | 15314  |
|5    | A          | 451    |
|6    | A          | 513    |
|7    | B          | 15     |
|8    | B          | 13     |
|9    | B          | 1154   |
|10   | B          | 1345   |
|11   | B          | 1221   |
|12   | B          | 234    |
|13   | C          | 2345   |
|14   | C          | 2645   |
|15   | C          | 2645   |
|16   | C          | 2652   |
|17   | C          | 65     |
+-----+------------+--------+

SELECT t1.id,t1.company,MAX(t1.salary) AS salary
FROM employee t1
LEFT JOIN employee t2
ON t1.company = t2.company
GROUP BY t1.company,t1.id
HAVING SUM(CASE WHEN t1.salary = t2.salary THEN 1 ELSE 0 END) >= ABS(SUM(SIGN(t1.salary - t2.salary)))
ORDER BY t1.id