日常工作可能会用到的sql函数

----case when
select id,(
    case
        when Max(price) <= 100 then 1
        when Max(price) <= 200 then 2
        when Max(price) <= 300 then 3
        when Max(price) <= 400 then 4
        when Max(price) <= 500 then 5
        when Max(price) <= 600 then 6
    else
        7
    end
) as max_price
from table 
where date >= '2024-04-01' and date <= '2024-04-20'
group by id


----为空时填充0
coalesce(price,0)

----中位数近似值
approx_percentile(price,0.5)

#留存计算
select date,
SUM(CASE days WHEN 0  then nums ELSE 0 END) AS day_0 ,
SUM(CASE days WHEN 1  then nums ELSE 0 END) AS day_1 ,
SUM(CASE days WHEN 2  then nums ELSE 0 END) AS day_2 ,
SUM(CASE days WHEN 3  then nums ELSE 0 END) AS day_3 ,
SUM(CASE days WHEN 4  then nums ELSE 0 END) AS day_4  
FROM
(select COUNT(distinct t1.uid) nums,t1.date,date_diff('day',cast(t1.date as date),cast(t2.date as date)) days from register t1
inner join login t2
on t1.uid=t2.uid
WHERE t1.date>='2024-04-01' and t1.date<='2024-04-20'
 and  t2.date>='2024-04-01' and t2.date<='2024-04-20'
group by t1.date,date_diff('day',cast(t1.date as date),cast(t2.date as date)))
group by date
order by date desc


#求最大值 (max(x) over())
SELECT id,price,(MAX (price) over()) max_price from table WHERE date >= '2024-04-01' and date <= '2024-04-20'

#最大值+序号 (row_numer() over(order by x) + max_id)
SELECT id,price,(row_number() over(ORDER by price) + max_price) from
(SELECT id,price,(MAX (price) over()) max_price from table WHERE date >= '2024-04-01' and date <= '2024-04-20') tmp



#字段拆分
#msg_detail=['{"id": 120128001, "num": 1, "position": 10, "type": 0}','{"id": 120528001, "num": 1, "position": 10, "type": 0}']
SELECT 
   uid,
   json_query(msg,'$.id') as id,
   json_query(msg,'$.num') as num,
   json_query(msg,'$.position') as position,
   json_query(msg,'$.type') as type
FROM 
  (SELECT uid,cast(parse_json(msg_detail) as array<string>) as msg_detail FROM table where date >= '2024-04-01' and date <= '2024-04-20') tmp
CROSS JOIN UNNEST (msg_detail) AS t(msg)


#求当前行数据到首行汇总值
#field1 field2 sum_field
#1     10      10
#2     20      30
#3     30      60
#4     40      100
SELECT 
    field1, 
    field2, 
    SUM(field2) OVER (ORDER BY field1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_field
FROM 
    table;