如何充分发挥 SQL 能力？

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

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

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

-- SQL - 4
select sequence(1, 3, 1);

-- result
[1, 2, 3]

-- SQL - 5
with 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 - 6
with 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_count
from (
    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)
    )
) t
join group_dimension on t.group_id = group_dimension.group_id
order by group_dimension.dimension_name;

-- SQL - 7
select
    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 - 8
with 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_date
from (
    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 - 9
with 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_date
from (
    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
) t
group by cont;

-- SQL - 10
with 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 - 11
select 
    date_item as day, 
    sum(repayment) as total_repayment
from (
    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
) t
where date_item >= '2024-01-15' and date_item <= '2024-01-16'
group by date_item
order by date_item;

-- SQL - 12
select 
    concat(val1, val2) as perm
from (select split('A,B,C', ',') as characters) dummy
lateral view explode(characters) t1 as val1
lateral view explode(characters) t2 as val2;

-- SQL - 13
select 
    concat(least(val1, val2), greatest(val1, val2)) as comb
from (select split('A,B,C', ',') as characters) dummy
lateral view explode(characters) t1 as val1
lateral view explode(characters) t2 as val2
group by least(val1, val2), greatest(val1, val2);

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

-- result
[['foo', 'bar'], ['foo', 'boo']['bar', 'boo']]

-- SQL - 15
with 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 - 16
select /*+ mapjoin(combs) */
    combs.strategy_comb,
    so.strategy,
    so.order_cnt
from strategy_order so
join ( -- 生成所有对比组
    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 = 1
where so.strategy in (combs.strategy_1, combs.strategy_2)
order by combs.strategy_comb, so.strategy;

-- SQL - 17
with 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 - 18
select user_id
from (
    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
) t1
where datediff(dt, lag_dt) + 1 = 2
group by user_id;

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

-- SQL - 20
select user_id
from (
    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
) t2
where cont_days >= 2
group by user_id;

-- SQL - 21
select user_id
from (
    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
) t2
where datediff(cont_date_end, cont_date_start) + 1 >= 2
group by user_id;

-- SQL - 22
with 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 - 23
select
    user_id, 
    -- 最长连续活跃天数
    datediff(max(dt), min(dt)) + 1 as cont_days,
    -- 最长连续活跃日期区间
    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
order by cont_days desc
limit 2;