如何充分发挥 SQL 能力？

一、前言

二、数列

-- SQL - 1select    t.pos as a_nfrom (    select posexplode(split(space(3), space(1), false))) t;

-- SQL - 2select    a + t.pos * d as a_nfrom (    select posexplode(split(space(n - 1), space(1), false))) t;

-- SQL - 3select    a * pow(q, t.pos) as a_nfrom (    select posexplode(split(space(n - 1), space(1), false))) t;

-- SQL - 4select sequence(1, 3, 1);
-- result[1, 2, 3]

-- SQL - 5with visit_log as (    select stack (        6,        '2024-01-01', '101', '湖北', '武汉', 'Android',        '2024-01-01', '102', '湖南', '长沙', 'IOS',        '2024-01-01', '103', '四川', '成都', 'Windows',        '2024-01-02', '101', '湖北', '孝感', 'Mac',        '2024-01-02', '102', '湖南', '邵阳', 'Android',        '2024-01-03', '101', '湖北', '武汉', 'IOS'    )     -- 字段：日期，用户，省份，城市，设备类型    as (dt, user_id, province, city, device_type))select * from visit_log;

{ dim_name_1, dim_name_2, ..., dim_name_n }

GROUPING__ID：3 => { 0, 1, 1 }维度名称序列：{ 省份, 城市, 设备类型 }
映射：{ 0:省份, 1:城市, 1:设备类型 }
GROUPING__ID 为 3 的数据行聚合维度即为：省份

-- SQL - 6with group_dimension as (    select -- 每种分组对应的维度字段        gb.group_id, concat_ws(",", collect_list(case when gb.placeholder_bit = 0 then dim_col.val else null end)) as dimension_name    from (        select groups.pos as group_id, pe.*        from (            select posexplode(split(space(cast(pow(2, 3) as int) - 1), space(1), false))        ) groups -- 所有分组        lateral view posexplode(regexp_extract_all(lpad(conv(groups.pos,10,2), 3, "0"), '(0|1)')) pe as placeholder_idx, placeholder_bit -- 每个分组的bit信息    ) gb    left join ( -- 所有维度字段        select posexplode(split("省份,城市,设备类型", ','))    ) dim_col on gb.placeholder_idx = dim_col.pos    group by gb.group_id)select     group_dimension.dimension_name,    province, city, device_type,    visit_countfrom (    select        grouping_id(province, city, device_type) as group_id,        province, city, device_type,        count(1) as visit_count    from visit_log b    group by province, city, device_type    GROUPING SETS(        (province),        (province, city),        (province, city, device_type)    )) tjoin group_dimension on t.group_id = group_dimension.group_idorder by group_dimension.dimension_name;

三、区间

-- SQL - 7select    a + t.pos * d as sub_interval_start, -- 子区间起始值    a + (t.pos + 1) * d as sub_interval_end -- 子区间结束值from (    select posexplode(split(space(n - 1), space(1), false))) t;

-- SQL - 8with dummy_table as (    select stack(        2,        '2024-01-01', '2024-01-03',        '2024-01-02', '2024-01-04'    ) as (date_start, date_end))select     min(date_item) as date_start_merged,     max(date_item) as date_end_merged,     collect_set( -- 交叉日期计数        case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end    ) as overlap_datefrom (    select         -- 拆解后的单个日期        date_add(date_start, pos) as date_item,        -- 拆解后的单个日期出现的次数        count(1) over (partition by date_add(date_start, pos)) as date_item_cnt    from dummy_table    lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) t;

-- SQL - 9with dummy_table as (    select stack(        5,        '2024-01-01', '2024-01-03',        '2024-01-02', '2024-01-04',        '2024-01-06', '2024-01-08',        '2024-01-08', '2024-01-08',        '2024-01-07', '2024-01-10'    ) as (date_start, date_end))select    min(date_item) as date_start_merged,     max(date_item) as date_end_merged,    collect_set( -- 交叉日期计数        case when date_item_cnt > 1 then concat(date_item, ':', date_item_cnt) else null end    ) as overlap_datefrom (    select         -- 拆解后的单个日期        date_add(date_start, pos) as date_item,        -- 拆解后的单个日期出现的次数        count(1) over (partition by date_add(date_start, pos)) as date_item_cnt,        -- 对于拆解后的单个日期，重组为新区间的标记        date_add(date_add(date_start, pos), 1 - dense_rank() over (order by date_add(date_start, pos))) as cont    from dummy_table    lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) tgroup by cont;

-- SQL - 10with user_repayment as (    select stack(        3,        '101', '2024-01-01', '2024-01-15', 10,        '102', '2024-01-05', '2024-01-20', 20,        '103', '2024-01-10', '2024-01-25', 30    )     -- 字段：用户，开始日期，结束日期，每日还款金额    as (user_id, date_start, date_end, repayment))select * from user_repayment;

-- SQL - 11select     date_item as day,     sum(repayment) as total_repaymentfrom (    select         date_add(date_start, pos) as date_item,        repayment    from user_repayment    lateral view posexplode(split(space(datediff(date_end, date_start)), space(1), false)) t as pos, val) twhere date_item >= '2024-01-15' and date_item <= '2024-01-16'group by date_itemorder by date_item;

四、排列组合

-- SQL - 12select     concat(val1, val2) as permfrom (select split('A,B,C', ',') as characters) dummylateral view explode(characters) t1 as val1lateral view explode(characters) t2 as val2;

-- SQL - 13select     concat(least(val1, val2), greatest(val1, val2)) as combfrom (select split('A,B,C', ',') as characters) dummylateral view explode(characters) t1 as val1lateral view explode(characters) t2 as val2group by least(val1, val2), greatest(val1, val2);

-- SQL - 14select combinations(array('foo', 'bar', 'boo'),2);
-- result[['foo', 'bar'], ['foo', 'boo']['bar', 'boo']]

-- SQL - 15with strategy_order as (    select stack(        3,        '2024-01-01', 'Strategy A', 10,        '2024-01-01', 'Strategy B', 20,        '2024-01-01', 'Strategy C', 30    )     -- 字段：日期，投放策略，单量    as (dt, strategy, order_cnt))select * from strategy_order;

-- SQL - 16select /*+ mapjoin(combs) */    combs.strategy_comb,    so.strategy,    so.order_cntfrom strategy_order sojoin ( -- 生成所有对比组    select         concat(least(val1, val2), '-', greatest(val1, val2)) as strategy_comb,        least(val1, val2) as strategy_1, greatest(val1, val2) as strategy_2    from (        select collect_set(strategy) as strategies        from strategy_order    ) dummy    lateral view explode(strategies) t1 as val1    lateral view explode(strategies) t2 as val2    where val1 <> val2    group by least(val1, val2), greatest(val1, val2)) combs on 1 = 1where so.strategy in (combs.strategy_1, combs.strategy_2)order by combs.strategy_comb, so.strategy;

五、连续

-- SQL - 17with visit_log as (    select stack (        6,        '2024-01-01', '101', '湖北', '武汉', 'Android',        '2024-01-01', '102', '湖南', '长沙', 'IOS',        '2024-01-01', '103', '四川', '成都', 'Windows',        '2024-01-02', '101', '湖北', '孝感', 'Mac',        '2024-01-02', '102', '湖南', '邵阳', 'Android',        '2024-01-03', '101', '湖北', '武汉', 'IOS'    )     -- 字段：日期，用户，省份，城市，设备类型    as (dt, user_id, province, city, device_type))select * from visit_log;

-- SQL - 18select user_idfrom (    select         *,        lag(dt, 2 - 1) over (partition by user_id order by dt) as lag_dt    from (select dt, user_id from visit_log group by dt, user_id) t0) t1where datediff(dt, lag_dt) + 1 = 2group by user_id;

-- SQL - 19select user_idfrom (    select *,         dense_rank() over (partition by user_id order by dt) as dr    from visit_log) t1where datediff(dt, date_add(dt, 1 - dr)) + 1 = 2group by user_id;

-- SQL - 20select user_idfrom (    select         *,        -- 连续活跃天数        count(distinct dt)             over (partition by user_id, cont) as cont_days    from (        select             *,             date_add(dt, 1 - dense_rank()                 over (partition by user_id order by dt)) as cont        from visit_log    ) t1) t2where cont_days >= 2group by user_id;

-- SQL - 21select user_idfrom (    select         user_id, cont,         -- 连续活跃区间        min(dt) as cont_date_start, max(dt) as cont_date_end    from (        select             *,             date_add(dt, 1 - dense_rank()                 over (partition by user_id order by dt)) as cont        from visit_log    ) t1    group by user_id, cont) t2where datediff(cont_date_end, cont_date_start) + 1 >= 2group by user_id;

-- SQL - 22with visit_log as (    select stack (        6,        '2024-01-01', '101', '湖北', '武汉', 'Android',        '2024-01-01', '102', '湖南', '长沙', 'IOS',        '2024-01-01', '103', '四川', '成都', 'Windows',        '2024-01-02', '101', '湖北', '孝感', 'Mac',        '2024-01-02', '102', '湖南', '邵阳', 'Android',        '2024-01-03', '101', '湖北', '武汉', 'IOS'    )     -- 字段：日期，用户，省份，城市，设备类型    as (dt, user_id, province, city, device_type))select * from visit_log;

-- SQL - 23select    user_id,     -- 最长连续活跃天数    datediff(max(dt), min(dt)) + 1 as cont_days,    -- 最长连续活跃日期区间    min(dt) as cont_date_start, max(dt) as cont_date_endfrom (    select         *,         date_add(dt, 1 - dense_rank()             over (partition by user_id order by dt)) as cont    from visit_log) t1group by user_id, contorder by cont_days desclimit 2;

六、扩展

-- SQL - 24with user_wifi_log as (    select stack (        9,        '2024-01-01 10:01:00', '101', 'cmcc-Starbucks', 'scan', -- 扫描        '2024-01-01 10:02:00', '101', 'cmcc-Starbucks', 'scan',        '2024-01-01 10:03:00', '101', 'cmcc-Starbucks', 'scan',        '2024-01-01 10:04:00', '101', 'cmcc-Starbucks', 'conn', -- 连接        '2024-01-01 10:05:00', '101', 'cmcc-Starbucks', 'conn',        '2024-01-01 10:06:00', '101', 'cmcc-Starbucks', 'conn',        '2024-01-01 11:01:00', '101', 'cmcc-Starbucks', 'conn',        '2024-01-01 11:02:00', '101', 'cmcc-Starbucks', 'conn',        '2024-01-01 11:03:00', '101', 'cmcc-Starbucks', 'conn'    )     -- 字段：时间，用户，WiFi，状态（扫描、连接）    as (time, user_id, wifi, status))select * from user_wifi_log;

-- SQL - 25select     user_id,     wifi,    max(status) as status,    min(time) as start_time,     max(time) as end_timefrom (    select *,        max(if(lag_status is null or lag_time is null or status <> lag_status or datediff(time, lag_time, 'ss') > 60 * 30, rn, null))             over (partition by user_id, wifi order by time) as group_idx    from (        select *,            row_number() over (partition by user_id, wifi order by time) as rn,            lag(time, 1) over (partition by user_id, wifi order by time) as lag_time,            lag(status, 1) over (partition by user_id, wifi order by time) as lag_status        from user_wifi_log    ) t1) t2group by user_id, wifi, group_idx;

结语