该博客文章详细解析了一段复杂的SQL查询代码，用于统计两个数据源taw和tcn中用户按日期和等级的登录次数。通过全连接操作，合并了登录开户和销户的数据，并计算了每日的增减趋势。内容涵盖了日期聚合、条件求和及窗口函数的应用，为数据分析和业务理解提供了深入洞察。
SQL codewith tmp_a as
(select distinct nvl(m.rdate, n.rdate),
max(case when m.rdate=n.rdate and m.userlevel = n.userlevel then m.rcnt_2+n.rcnt_2 else m.rcnt_2 end) "总数高级",
max(case when m.rdate=n.rdate and m.userlevel = n.userlevel then m.rcnt_1+n.rcnt_1 else m.rcnt_1 end) "总数初级"
from(select to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') rdate,
userlevel,
count(decode(userlevel,10,1)) rcnt_1,
count(decode(userlevel,11,1)) rcnt_2
from ta
where userlevel in(10,11)
group by userlevel, to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')
)m,
(select to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') rdate,
userlevel,
count(decode(userlevel,10,1)) rcnt_1,
count(decode(userlevel,11,1)) rcnt_2
from tc n
where userlevel in(10,11)
group by userlevel, to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')
)n
group by nvl(m.rdate, n.rdate)
),
tmp_b as(
select to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') rdate,
count(decode(userlevel,10,1)) rcnt_1,
count(decode(userlevel,11,1)) rcnt_2
from tb
where userlevel in(10,11)
group by to_char(to_date(logindate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')
)
select nvl(a.rdate,b.rdate) "时间",
nvl(a.rcnt_2,0) "开户高级",
nvl(a.rcnt_1,0) "开户初级",
nvl(b.rcnt_2,0) "销户高级",
nvl(b.rcnt_2,0) "销户初级",
sum(a.rcnt_2)over(order by nvl(a.rdate,b.rdate))-nvl(sum(b.rcnt_2)over(order by nvl(a.rdate,b.rdate)),0) "总数高级",
sum(a.rcnt_1)over(order by nvl(a.rdate,b.rdate))-nvl(sum(b.rcnt_1)over(order by nvl(a.rdate,b.rdate)),0) "总数初级"
from tmp_a a full join tmp_b b
on a.rdate=b.rdate
order by nvl(a.rdate,b.rdate);
扫一扫
1671
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
