分组评级日期混杂
帮同事写的一个SQL。
需求:取出上月每7天中状态字段各不相同的数据,其中月末的几天作为最后一组7天数据来处理。
--1 取出上月
select t.createtime, t.orderstate
from test t
where t.createtime between trunc(trunc(sysdate, 'month') - 1, 'month') and
(trunc(sysdate, 'month') - 1 / 24 / 60 / 60)
--每隔7天
select t.createtime,
t.orderstate,
trunc((to_number(to_char(createtime, 'dd')) - 1) / 7)
from test t
where t.createtime between trunc(trunc(sysdate, 'month') - 1, 'month') and
(trunc(sysdate, 'month') - 1 / 24 / 60 / 60)
--每组排名
select s.grp, s.denserank, min(s.createtime), min(s.orderstate)
from (select p.createtime,
p.orderstate,
p.grp,
dense_rank() over(partition by p.grp order by p.orderstate) denserank
from (select t.createtime,
t.orderstate,
trunc(case
when (to_number(to_char(createtime, 'dd')) - 1) > 27 then
27
else
(to_number(to_char(createtime, 'dd')) - 1)
end / 7) grp
from test t
where t.createtime between
trunc(trunc(sysdate, 'month') - 1, 'month') and
(trunc(sysdate, 'month') - 1 / 24 / 60 / 60)) p) s
group by s.grp, s.denserank
order by 1, 2
分组排重自关联
产品购买表。需求,找出购买了所有产品的同时,又购买的其他产品,及这些产品被所有人购买的次数。
with pro as
(select '用户A' userid, '产品100' proid
from dual
union all
select '用户A' userid, '产品101' proid
from dual
union all
select '用户B' userid, '产品100' proid
from dual
union all
select '用户B' userid, '产品101' proid
from dual
union all
select '用户C' userid, '产品100' proid
from dual
union all
select '用户C' userid, '产品100' proid
from dual
union all
select '用户C' userid, '产品101' proid
from dual
union all
select '用户C' userid, '产品101' proid
from dual
union all
select '用户C' userid, '产品105' proid
from dual
union all
select '用户E' userid, '产品101' proid
from dual
union all
select '用户D' userid, '产品102' proid from dual)
select currentpid "当前产品", proid "同时购买的其他产品", cnt "被购买次数"
from(
select p3.currentpid, p4.proid, count(p4.proid) cnt, row_number() over(partition by p3.currentpid
order by p4.proid) rno
from(
select distinct s.proid currentpid, t.proid twicepid
from(
select distinct p0.proid, p1.userid
from pro p0
left join pro p1
on p0.proid = p1.proid) s
left join pro t
on s.userid = t.userid and s.proid <> t.proid) p3
left join pro p4
on p3.twicepid = p4.proid
group by p3.currentpid, p4.proid)
where not(rno <> 1 and proid is null)
order by 1, 3 desc;
输出结构
"当前产品","同时购买的其他产品","被购买次数"
"产品100","产品101","5"
"产品100","产品105","1"
"产品101","产品100","4"
"产品101","产品105","1"
"产品102","","0"
"产品105","产品101","5"
"产品105","产品100","4"
"产品100","产品101","5"
"产品100","产品105","1"
"产品101","产品100","4"
"产品101","产品105","1"
"产品102","","0"
"产品105","产品101","5"
"产品105","产品100","4"
递归查询与行列转换
with china as
(select 1 no, '中国' name, 0 pno
from dual
union all
select 10 no, '北京' name, 1 pno
from dual
union all
select 20 no, '上海' name, 1 pno
from dual
union all
select 30 no, '天津' name, 1 pno
from dual
union all
select 101 no, '海淀' name, 10 pno
from dual
union all
select 102 no, '朝阳' name, 10 pno
from dual
union all
select 1010 no, '中关村' name, 101 pno
from dual
union all
select 201 no, '闵行' name, 20 pno
from dual
union all
select 301 no, '塘沽' name, 30 pno from dual)
--select * from china order by no
select no, name, ltrim(sys_connect_by_path(name, '>'), '>')
from china
connect by prior no = pno
start with no = 1
order by no
NO NAME LTRIM(SYS_CONNECT_BY_PATH(NAME
---------- ------------ --------------------------------------------------------------------------------
1 中国 中国
10 北京 中国>北京
20 上海 中国>上海
30 天津 中国>天津
101 海淀 中国>北京>海淀
102 朝阳 中国>北京>朝阳
201 闵行 中国>上海>闵行
301 塘沽 中国>天津>塘沽
1010 中关村 中国>北京>海淀>中关村