トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-140 前と同じ値なら、詰めて出力

SQLパズル

BreakTestテーブル
ID  ColA  ColB  ColC  ColD  ColE
--  ----  ----  ----  ----  ----
 1  AAAA  9999  cccc  9999  AAAA
 2  AAAA  5555  eeee  9999  AAAA
 3  HHHH  3333  eeee  9999  BBBB
 4  FFFF  4444  cccc  9999  BBBB
 5  FFFF  4444  cccc  1111  CCCC

IDの昇順に出力するが、
前と同じ値なら、詰めて出力する

出力結果
ID  ColA  ColB  ColC  ColD  ColE
--  ----  ----  ----  ----  ----
 1  AAAA  9999  cccc  9999  AAAA
 2  HHHH  5555  eeee  1111  BBBB
 3  FFFF  3333  cccc  null  CCCC
 4  null  4444  null  null  null

こちらを参考にさせていただきました


データ作成スクリプト

create table BreakTest(ID,ColA,ColB,ColC,ColD,ColE) as
select 1,'AAAA',9999,'cccc',9999,'AAAA' from dual union
select 2,'AAAA',5555,'eeee',9999,'AAAA' from dual union
select 3,'HHHH',3333,'eeee',9999,'BBBB' from dual union
select 4,'FFFF',4444,'cccc',9999,'BBBB' from dual union
select 5,'FFFF',4444,'cccc',1111,'CCCC' from dual;


SQL

--■■■withで中間表を作る方法■■■
with Work as (
select ID,ColA,ColB,ColC,ColD,ColE,
count(ColA) over(order by ID) as ColACount,
count(ColB) over(order by ID) as ColBCount,
count(ColC) over(order by ID) as ColCCount,
count(ColD) over(order by ID) as ColDCount,
count(ColE) over(order by ID) as ColECount
from (select ID,
      nullif(ColA,Lag(ColA) over(order by ID)) as ColA,
      nullif(ColB,Lag(ColB) over(order by ID)) as ColB,
      nullif(ColC,Lag(ColC) over(order by ID)) as ColC,
      nullif(ColD,Lag(ColD) over(order by ID)) as ColD,
      nullif(ColE,Lag(ColE) over(order by ID)) as ColE
      from BreakTest))
select m.ID,a.ColA,b.ColB,c.ColC,d.ColD,e.ColE
  from BreakTest m
  Left Join Work a on m.ID = a.ColACount and a.ColA is not null
  Left Join Work b on m.ID = b.ColBCount and b.ColB is not null
  Left Join Work c on m.ID = c.ColCCount and c.ColC is not null
  Left Join Work d on m.ID = d.ColDCount and d.ColD is not null
  Left Join Work e on m.ID = e.ColECount and e.ColE is not null
 where a.ColA is not null
    or b.ColB is not null
    or c.ColC is not null
    or d.ColD is not null
    or e.ColE is not null
order by ID;

--■■■rangeを使う方法■■■
select ID,ColA,ColB,ColC,ColD,ColE
from (select ID,
      case when ID <= sum(FlagA) over()
           then max(ColA) over(order by SumA
                               range between ID-SumA following
                                         and ID-SumA following) end as ColA,
      case when ID <= sum(FlagB) over()
           then max(ColB) over(order by SumB
                               range between ID-SumB following
                                         and ID-SumB following) end as ColB,
      case when ID <= sum(FlagC) over()
           then max(ColC) over(order by SumC
                               range between ID-SumC following
                                         and ID-SumC following) end as ColC,
      case when ID <= sum(FlagD) over()
           then max(ColD) over(order by SumD
                               range between ID-SumD following
                                         and ID-SumD following) end as ColD,
      case when ID <= sum(FlagE) over()
           then max(ColE) over(order by SumE
                               range between ID-SumE following
                                         and ID-SumE following) end as ColE
      from (select ID,ColA,ColB,ColC,ColD,ColE,
            FlagA,FlagB,FlagC,FlagD,FlagE,
            case FlagA when 1 then sum(FlagA) over(order by ID) else 0 end as SumA,
            case FlagB when 1 then sum(FlagB) over(order by ID) else 0 end as SumB,
            case FlagC when 1 then sum(FlagC) over(order by ID) else 0 end as SumC,
            case FlagD when 1 then sum(FlagD) over(order by ID) else 0 end as SumD,
            case FlagE when 1 then sum(FlagE) over(order by ID) else 0 end as SumE
            from (select ID,ColA,ColB,ColC,ColD,ColE,
                  case when Lag(ColA) over(order by ID) = ColA then 0 else 1 end as FlagA,
                  case when Lag(ColB) over(order by ID) = ColB then 0 else 1 end as FlagB,
                  case when Lag(ColC) over(order by ID) = ColC then 0 else 1 end as FlagC,
                  case when Lag(ColD) over(order by ID) = ColD then 0 else 1 end as FlagD,
                  case when Lag(ColE) over(order by ID) = ColE then 0 else 1 end as FlagE
                    from BreakTest)))
 where ColA is not null
    or ColB is not null
    or ColC is not null
    or ColD is not null
    or ColE is not null
order by ID;


解説

withで中間表を作って、外部結合させてもいいし、
rangeを使ってもいいでしょう。

10-231 dense_rankがRowNum番目の値を取得
10-232 Row_Numberが列値番目の値を取得