This discussion is archived
8 Replies Latest reply: Nov 11, 2013 5:18 AM by Aketi Jyuuzou RSS

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

84857f67-f78c-4b8d-a3ca-5d8be4e6ee28 Newbie
Currently Being Moderated

以下のようなデータが存在する時、
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 Explorer
    Currently Being Moderated

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

    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 Pro
    Currently Being Moderated

    > 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: 連続する項目の件数をカウントするには?
    84857f67-f78c-4b8d-a3ca-5d8be4e6ee28 Newbie
    Currently Being Moderated

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

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

     

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

    勉強になりました。

  • 4. Re: 連続する項目の件数をカウントするには?
    84857f67-f78c-4b8d-a3ca-5d8be4e6ee28 Newbie
    Currently Being Moderated

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

     

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

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

     

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

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

    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: 連続する項目の件数をカウントするには?
    84857f67-f78c-4b8d-a3ca-5d8be4e6ee28 Newbie
    Currently Being Moderated

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

    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 Expert
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    Oh thanks for MATCH_RECOGNIZE solution

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points