8 Replies Latest reply: Nov 11, 2013 7:18 AM by Aketi Jyuuzou RSS

    連続する項目の件数をカウントするには?

    1049968

      以下のようなデータが存在する時、
      A列、B列でソートし、C列が連続する行の先頭レコードを取得し、連続する項目の件数をカウントすることは可能でしょうか?


      【テーブル】

      A    B    C  
      ---- ---- ----
      500  1    A
      500  2    A
      500  3    B
      500  4    B
      500  5    A
      600  1    A
      600  2    A
      600  3    B

       

       

       

      【取得したい結果】
      A    B    C    Count
      ---- ---- ---- ----
      500  1    A    2
      500  3    B    2
      500  5    A    1
      600  1    A    2
      600  3    B    1

       

      A列、B列でソートし、C列が連続する行の先頭レコードを取得するところまではできたのですが、
      件数をカウントする方法が思いつきません。

       

      SELECT A,B,C
      FROM(SELECT A,B,C
              ,NVL(lag(C) over (partition by A order by B),'X' ) AS LAST_A
              FROM テーブル)
      WHERE C <> LAST_A

       

      もし良い知恵があれば教えてください。
      よろしくお願いします。

       

      Oracle 10g

        • 1. Re: 連続する項目の件数をカウントするには?
          yamada

          もっとキレイに書けるのだと思うのですが、自分がパッと思いついたのは

          select
              min(A)   as A
            , min(B)   as B
            , min(C)   as C
            , COUNT(*) as 件数
          from
              (
                  select
                      A
                    , B
                    , C
                    , CHANGED
                    , SUM(CHANGED) over(order by A, B) as GROUP_NO
                  from
                      (
                          select
                              A
                            , B
                            , C
                            , case
                                  when C <> NVL(LAG(C) over (partition by A order by B), 'X')
                                  then 1
                                  else 0
                              end as CHANGED
                          from
                              テーブル
                      )
              )
          group by
              GROUP_NO
          order by
              A
            , B
            , C;
          

          こんな感じのSQLです。

          Cが1レコード前と変わったらフラグを立て、その数ごとにグルーピングして集計する、という考えです。

          • 2. Re: 連続する項目の件数をカウントするには?
            user12075536123

            > A列、B列でソートし、C列が連続する行の先頭レコードを取得し、連続する項目の件数をカウントすることは可能でしょうか?

             

            このルールだと以下の4行が先頭レコードになると思います。

            もう少し条件があるのではないでしょうか?

             

            A    B    C  
            ---- ---- ----
            500  1    A   ←※
            500  2    A
            500  3    B   ←※
            500  4    B
            500  5    A   ←※
            600  1    A
            600  2    A
            600  3    B   ←※

            • 3. Re: 連続する項目の件数をカウントするには?
              1049968

              回答ありがとうございます。

              教えていただいたSQLで取得することができました。

               

              フラグを立ててグループ化する方法もあるんですね。

              勉強になりました。

              • 4. Re: 連続する項目の件数をカウントするには?
                1049968

                すみません。条件文だけ読むとその通りですね。

                 

                条件は、
                ・A列、B列でソートし、C列が連続する行の先頭レコードを取得し、連続する項目の件数をカウントする

                ・ただし、A列が変わる場合は、連続しているとみなさない。

                 

                というところでしょうか。

                • 5. Re: 連続する項目の件数をカウントするには?
                  Aketi Jyuuzou

                  US-OTNで旅人算メソッドと呼ばれている方法を使ってみました

                  https://forums.oracle.com/thread/1007478

                   

                  with t(ColA,ColB,ColC) as(

                  select 500,1,'A' from dual union

                  select 500,2,'A' from dual union

                  select 500,3,'B' from dual union

                  select 500,4,'B' from dual union

                  select 500,5,'A' from dual union

                  select 600,1,'A' from dual union

                  select 600,2,'A' from dual union

                  select 600,3,'B' from dual),

                  tmp as(

                  select ColA,ColB,ColC,

                  Row_Number() over(order by ColA,ColB)

                  -Row_Number() over(partition by ColA,ColC order by ColB) as Distance

                    from t)

                  select ColA,min(ColB),ColC,count(*)

                    from tmp

                  group by ColA,ColC,Distance

                  order by ColA,min(ColB);

                   

                  COLA  MIN(COLB)  C  COUNT(*)

                  ----  ---------  -  --------

                  500          1  A         2

                  500          3  B         2

                  500          5  A         1

                  600          1  A         2

                  600          3  B         1

                   

                  Oracle12cからは、Match_Recognize句でシンプルに書けるようですが、

                  私は、Match_Recognize句について、まだ勉強中なのであった

                  • 6. Re: 連続する項目の件数をカウントするには?
                    1049968

                    回答ありがとうございます。

                    With句を使ったことがなかったため、読み解くのに少し時間がかかってしまいました(^^;

                     

                    私の記述のテーブルがあるとすると、こうですね。

                    With tmp as

                        (select A, B, C,

                         Row_Number() over(order by A, B) - Row_Number() over(partition by A,C order by B) as Distance

                         from テーブル)

                    select A, min(B), C, count(*)

                    from tmp

                    group by A, C, Distance

                    order by A, min(B);

                     

                    この方法だと、かなり短く書くことができますね。

                    Match_Recognize句も気になりますが、今回の環境は10gなので、こちらを採用させてもらおうと思います。

                     

                    ありがとうございました。

                    • 7. Re: 連続する項目の件数をカウントするには?
                      Stew Ashton

                      With Oracle 12c MATCH_RECOGNIZE:

                       

                      with t(ColA,ColB,ColC) as(
                        select 500,1,'A' from dual union
                        select 500,2,'A' from dual union
                        select 500,3,'B' from dual union
                        select 500,4,'B' from dual union
                        select 500,5,'A' from dual union
                        select 600,1,'A' from dual union
                        select 600,2,'A' from dual union
                        SELECT 600,3,'B' FROM DUAL
                      )
                      SELECT * FROM T
                      MATCH_RECOGNIZE(
                        PARTITION BY COLA ORDER BY COLB
                        MEASURES FIRST(COLB) FIRST_COLB, COLC COLC, COUNT(*) CNT
                        PATTERN(A b*)
                        define b as colc = prev(colc)
                      );
                      

                       

                      ONE ROW PER MATCH is the default.

                       

                      My respects to the tabibitosan master!

                      Stew Ashton

                      • 8. Re: 連続する項目の件数をカウントするには?
                        Aketi Jyuuzou

                        Oh thanks for MATCH_RECOGNIZE solution

                        I will get Oracle12c And I will try to understand your solution.