sql 字段相加_业务sql技巧总结-CSDN博客

小sql技巧

求表中某个细粒度名词XXXX次数，使用sum(if(表达式,1,0))

如求评论表中好评次数

这里是没办法通过count(*)统计

sum(if(appraise='好评',1,0))

求表中某东西的统计XXXX次数使用count(*)

如求登录次数

count(login_id)

dwt取id，有新id取新id，为空取旧id

如求用户id

nvl(new.user_id,old.user_id)

dwt求首次时间，新表今天有登录且旧的首登为空取今天时间，否则取旧时间

如求用户首次登录时间

if(old.first_login_time is null and new.login_count>0,'2020-04-27',old.first_login_time)

dwt求最后时间，新表今天有登录则取今天时间，否则取旧时间

如求用户最后登录时间

if(new.login_count>0,'2020-04-27',old.last_login_time)

dwt求总天数，旧+新，因为是full out join，所以要加上空判断

如求总的登录天数

nvl(old.login_count,0)+if(new.login_count>0,1,0)

dwt新表求单天数据sum(if(dt='当天日期'，count，0))

如求当天登录次数

sum(if(dt='2020-04-27',login_count,0)) login_count

dwt累积30天等指标在新数据表new中进行累加 where最近30天

大sql技巧

dws层的多表join

涉及的表比较多可以采用with tmp的方法

大体结构如下

利用with tmp技巧取多张join表数据，再通过union all 空位补0思想构成最后的sql

注意点，空位补0一定要添加字段名，否则无法解析

with
tmp_login as
(
select
    pid
    count(*) login_count
from 
    dwd_start_log
where 
    dt='2020-04-27'
    and user_id is not null
group by 
    user_id
),
tmp_cart as
(
......
),
tmp_order as
(
......
) ,
tmp_payment as
(
......
)
insert overwrite table dws_user_action_daycount 
partition(dt='2020-04-27')
select
    user_actions.user_id,
    sum(user_actions.login_count),
    sum(user_actions.cart_count),
    ......
from
(
select
    user_id,
    login_count,
    0 cart_count,
    ......
from
    tmp_login
union all
select
    user_id,
    0 login_count,
    cart_count,
    ......
from
    tmp_cart
union all
    ......
) user_actions
group by user_id;

dwt层累计表的新旧join

大体结构如下

查询旧表

full out join 新表

在新表中计算累加值，所以这里where条件要取最近30天的时间

二者join结果进行判断或相加

insert overwrite table dwt_user_topic
select
    nvl(),
    if(),
    nvl()+if(),
    nvl()+nvl(),
    ......
from
    dwt_user_topic old
full outer join
    (
    select
        user_id,
        sum() ,
        sum() ,
        ......
        from dws_user_action_daycount
        where dt>=date_add( '2020-04-27',-30)
        group by user_id
    )new
on 
    old.user_id=new.user_id;