Forum Stats

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

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

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

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

· Share on TwitterShare on Facebook

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

    · Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook
  • user12075536123
    user12075536123 Member Posts: 346

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

    · Share on TwitterShare on Facebook
  • 1049968
    1049968 Member Posts: 4

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

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

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

    勉強になりました。

    · Share on TwitterShare on Facebook
  • 1049968
    1049968 Member Posts: 4

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

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

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

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

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

    · Share on TwitterShare on Facebook
  • 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句も気になりますが、今回の環境は10ｇなので、こちらを採用させてもらおうと思います。

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

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
This discussion has been closed.