Oracle 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 noselect no, name, ltrim(sys_connect_by_path(name, '>'), '>')  from chinaconnect 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 中关村       中国>北京>海淀>中关村