SQL技巧精选-优快云博客

分组评级日期混杂

帮同事写的一个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"

递归查询与行列转换

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 中关村中国>北京>海淀>中关村