一些SQL语法保存

11111

排序

row_number() over(PARTITION BY worksheet_id ORDER BY id DESC)

取余数

when ((`违规次数`-1)%3 <>0

把几行内容根据某个字段合并一行

concat_ws('-',collect_set(`角色`)) as `角色`

判断有无特定字符并替换

if(instr(user_name,"-")>0,reverse(split(reverse(user_name),'-')[0]),user_name) as `admin_name`

数组存储数据拆分json

select
id,
concat_ws('。',
    collect_set(concat_ws('-',startTime,endTime,text))
) as `对话内容`
from
    (
    select
        t1.id
        ,t1.violateInfoLists
       ,t2.violateInfoList
    --   ,concat_ws('-'
    --         ,from_unixtime(cast(get_json_object(t2.violateInfoList,'$.violateStartTime') as bigint),'HH:mm:ss')
    --         ,from_unixtime(cast(get_json_object(t2.violateInfoList,'$.violateEndTime') as bigint),'HH:mm:ss')
    --         ,get_json_object(t2.violateInfoList,'$.violateNote')
    --         ) as text
    --   ,concat_ws('-'
    --         ,from_unixtime(cast(get_json_object(t2.violateInfoList,'$.startTime') as bigint),'HH:mm:ss')
    --         ,from_unixtime(cast(get_json_object(t2.violateInfoList,'$.endTime') as bigint),'HH:mm:ss')
    --         ,get_json_object(t2.violateInfoList,'$.text')
    --         ) as text
      ,from_unixtime(cast(get_json_object(t2.violateInfoList,'$.violateStartTime') as bigint),'HH:mm:ss') as startTime
      ,from_unixtime(cast(get_json_object(t2.violateInfoList,'$.violateEndTime') as bigint),'HH:mm:ss') as endTime
      ,get_json_object(t2.violateInfoList,'$.violateNote') as text   
    from
        (SELECT
          `id`,
          `extra_info`,
           get_json_object(extra_info,'$.violateInfoList') as violateInfoLists
        FROM ks_origin_plateco_db.gifshow_judge_task_dt_snapshot
        WHERE dt = '2021-09-12'
        and queue_id=45
        and result_value not like '%无违规%'
        ) t1
    lateral view explode(brickhouse.json_split(violateInfoLists)) t2 as violateInfoList
    --group by
)main
group by
id

json获取某个内容

get_json_object(extra_info,'$.itemInfo.remark')

case when 以及时间处理的用法

select
`进审日期`,
`主播级别`,
count(distinct `直播ID`) as `推审场次`,
sum(`直播时长min`) as `推审时长`
from
(
    select
    min(from_unixtime(cast(t_dingfang.push_time/1000 AS bigint),'yyyy-MM-dd')) as `进审日期`,
    case split(concat_ws('-',collect_set(t_dingfang.`主播级别`)),'-')[0]
        when "辛巴团队" then "辛巴团队" 
        when "顶级主播" then "顶级主播" 
        when "电商临时" then "电商临时" 
        when "中级主播" then "中级主播" 
        else concat_ws('-',collect_set(t_dingfang.`主播级别`)) end 
    as `主播级别`,
    t_dingfang.`直播ID`,
    t_dingfang.`主播ID`,
    t_live.`直播时长min` as `直播时长min`,
    t_live.`直播开始时间`
    from
    (
        select
        *
        from
        (
            select
                row_number() over(partition by uniq_id order by id desc) as uniq_desc,
                uniq_id as `uniq_id`,
                key1 as `主播ID`,
                long2 as `直播ID`,
                admin_id as `admin_id`,
                get_json_object(review_info,'$.tabTypeDesc') as `主播级别`,
                action,
                push_time,admin_time,
                from_unixtime(cast(push_time/1000 AS bigint)) as push_timem,
                from_unixtime(cast(admin_time/1000 AS bigint)) as admin_timem,
                from_unixtime(cast(long3/1000 AS bigint)) AS `直播开始时间`
                FROM ks_origin_risk_db.gifshow_audit_commerce_snapshot_live_patrol2_0_dt_snapshot
                --ks_origin_risk_db.gifshow_audit_commerce_log_live_patrol2_0_dt_snapshot
            where dt='{{ds}}'
            AND from_unixtime(cast(push_time/1000 AS bigint),'yyyy-MM-dd') ='{{ds}}'
            --between '${start}' and '${end}'
            and action =0--,15031领取15032释放15122通过15123拒绝15124返回修改
            and get_json_object(review_info,'$.tabTypeDesc') in ("辛巴团队","顶级主播","中级主播","电商临时")
            and key1<>950226217
        )tt where uniq_desc=1
    )t_dingfang
    left join
    (
        SELECT distinct
            case product
                when 'KUAISHOU' then '快手主APP'
                when 'NEBULA' then '极速版'
                when 'KWAI' then '快手海外版'
                when 'THANOS' then '大屏版'
                when 'KWAI_LITE' then '快手印尼版'
                when 'KWAI_GO' then '快手印度版'
                when 'KUAISHOU_LIVE_MATE' then '快手直播助手'
                when 'UNKNOWN' then '未知'
                else '未知' end
            AS `产品`,
            `live_id`,
            start_timestamp AS `直播开始时间`,
            if(hour(start_timestamp) between 1 and 9,1,0) as `12点后开播`,
            end_timestamp AS `直播结束时间`,
            `live_duration`/1000/60 AS `直播时长min`,
            `author_id` AS `主播ID`
        FROM
          kscdm.dim_ks_live_all
        WHERE
          p_date = '{{ds_nodash}}'
          and to_date(start_timestamp)>='{{ds}}'
    )t_live
    on t_dingfang.`直播ID`=t_live.live_id
    group by
    t_dingfang.`直播ID`
    ,t_dingfang.`主播ID`
    ,t_live.`直播时长min`
    ,t_live.`直播开始时间`
    having (t_live.`直播时长min`>=5 or t_live.`直播时长min` is not null)
    and HOUR(t_live.`直播开始时间`)>=9
)t_mm
group by
`进审日期`,
`主播级别`

涉及到一些字符串处理的用法

                when scene_name like '%人工场景%' and substr(violation_desc,0,30) like '%小秘书%' then '小秘书'
                when scene_name like '%人工场景%' and violation_desc like '%品控%珠宝违规%' then '品控风险商家'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%探索运营专项%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%召回审大夜%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%假货问题客满%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%小时榜%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%家居%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%家电%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%服装%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%服饰%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%美妆%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%蟹%' then '品控'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%日常案例%' then '品控'
                when scene_name like '%人工场景%' and violation_desc like '%违规回查%出售假冒商品%' then '售假核查'
                when scene_name like '%人工场景%' and violation_desc like '%闪电购%' then '闪电购'
                when scene_name like '%人工场景%' and violation_desc like '%盲盒%' then '虚假承诺'
                when scene_name like '%人工场景%' and length(violation_desc)=0 and rule_name like '%虚假交易%' then '其他'
                when scene_name like '%人工场景%' and violation_desc is null and rule_name like '%虚假交易%' then '其他'
                when scene_name like '%人工场景%' and length(violation_desc)=0 then '直播举报'
                when scene_name like '%人工场景%' and violation_desc is null then '直播举报'
                when scene_name like '%人工场景%' and split(split(violation_desc,'-')[1],'-')[0] like '%直播私下交易%' then '私下交易-直播间'
                when scene_name like '%人工场景%' then '未分类'
                when length(scene_name)=0 then '未分类'

日期减法

to_date(punish_ticket_create_date) between date_sub('${start_date}',29) and '${start_date}'