Forum Stats

  • 3,734,275 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

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

1049968
1049968 Member Posts: 4
edited Nov 11, 2013 8:16AM in データベース一般

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

Best Answer

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jan 9, 2014 2:52PM Accepted Answer

    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句について、まだ勉強中なのであった

Answers

  • yamada
    yamada Member Posts: 32

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

    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レコード前と変わったらフラグを立て、その数ごとにグルーピングして集計する、という考えです。

    yamada
  • > 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   ←※

  • 1049968
    1049968 Member Posts: 4

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

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

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

    勉強になりました。

  • 1049968
    1049968 Member Posts: 4

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

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

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

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

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jan 9, 2014 2:52PM Accepted Answer

    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句について、まだ勉強中なのであった

  • 1049968
    1049968 Member Posts: 4

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

    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なので、こちらを採用させてもらおうと思います。

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

  • Stew Ashton
    Stew Ashton Member Posts: 2,849 Gold Trophy

    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

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 11, 2013 8:18AM

    Oh thanks for MATCH_RECOGNIZE solution

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

This discussion has been closed.