質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.79%

SQLで特定カラム(複数)をGROUP BYした際に、COUNT関数で取れる数をNO GROUPの時にシーケンス的な扱いで連番を振りたい(表内重複許可/組合内重複不可_連番)

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,520

score 139

 解決策反映 

前提・実現したいこと

・使用環境:PstgreSQL v.9.5
・もはや、タイトルの通り。。。。SQLでSELECTしたい結果例を示しますので、そちらを参考に
・テーブルにGROUP BY でCOUNT出来る数と同じ感じにseqを付けたい

SQL内でsequenceを付けたい!
カラムAとカラムBが一致する、カラムC毎に連番を振る
※テーブルでユニークな連番ではなく、特定データグループ内での連番

table:item_use_history(使う対象テーブル:こいつをゴネゴネしたい)

id user_id datetime item_id value
1 101 2019/09/01 10:10:10 1 aaa1
2 102 2019/09/01 10:10:10 1 aaa2
3 103 2019/09/01 10:10:10 1 aaa3
4 101 2019/09/01 11:10:10 2 bbb1
5 102 2019/09/01 11:10:10 2 bbb1
6 103 2019/09/01 11:10:10 2 bbb2
7 101 2019/09/01 12:10:10 1 ccc1
8 102 2019/09/01 12:10:10 1 ccc2
9 103 2019/09/01 12:10:10 1 ccc3

select:goal_result(サブクエリで作成したい表)
最終結果...は、ただSELECTとJOINする想定なので、
実質は、下の表が作れるサブクエリを教えて欲しいです
seq箇所を何とか出したい。。。

id user_id datetime item_id value seq
1 101 2019/09/01 10:10:10 1 aaa1 1
2 102 2019/09/01 10:10:10 1 aaa2 1
3 103 2019/09/01 10:10:10 1 aaa3 1
4 101 2019/09/01 11:10:10 2 bbb1 1
5 102 2019/09/01 11:10:10 2 bbb1 1
6 103 2019/09/01 11:10:10 2 bbb2 1
7 101 2019/09/01 12:10:10 1 ccc1 2
8 102 2019/09/01 12:10:10 1 ccc2 2
9 103 2019/09/01 12:10:10 1 ccc3 2

select:try_result(まず、カウントしてみましたの結果表)

user_id use_date item_id count(item_id)
101 2019/09/01 1 2
102 2019/09/01 1 2
103 2019/09/01 1 2
101 2019/09/01 2 1
102 2019/09/01 2 1
103 2019/09/01 2 1

直面している課題

SQLが思いつかない。。。知恵を貸してください!

該当のソースコード

 goal_result 
これは書きかけ。。。まだ、想像すら出来ていないっす。
こんな感じで出来ました

WITH item_count AS ( 
  SELECT *, RANK() OVER (PARTITION BY i.use_date, i.user_id, i.item_id ORDER BY i.datetime) AS seq
  FROM (
    SELECT
      item.*,
      TO_CHAR(item.datetime::TIMESTAMP, 'YYYY/MM/DD') AS use_date
    FROM
      item_use_history AS item
    WHERE
      item.datetime BETWEEN ('2019/09/01 00:00:00' AND '2019/09/30 23:59:59')
  ) AS i
  WHERE TRUE =TRUE
  --GROUP BY 
  --ORDER BY 
)
SELECT *
FROM item_count
WHERE TRUE = TRUE
--GROUP BY 
--ORDER BY 

 try_result 
これは、普通にできる。。。

SELECT
  item.user_id,
  TO_CHAR(item.datetime::TIMESTAMP, 'YYYY/MM/DD') AS use_date,
  item.item_id,
  count(item_id)
FROM
  item_use_history AS item
WHERE
  i.datetime BETWEEN ('2019/09/01 00:00:00' AND '2019/09/30 23:59:59')
GROUP BY
  item.user_id, use_date, item.item_id
ORDER BY
  use_dateASC,
  item.user_id ASC,
  item.item_id ASC,

調べている情報

row_number() over()
これを調べているところ

過不足あれば、コメントくださいませ

最終的なゴール

こんな表にしたい!!

user_id use_date item_id seq_1 seq_1_value seq_2 seq_2_value ...
101 2019/09/01 1 1 aaa1 2 ccc1 ...
102 2019/09/01 1 1 aaa2 2 ccc1 ...
103 2019/09/01 1 1 aaa3 2 ccc1 ...
101 2019/09/01 2 1 bbb2 NULL NULL ...
102 2019/09/01 2 1 bbb2 NULL NULL ...
103 2019/09/01 2 1 bbb2 NULL NULL ...

なので、その前にこんな感じに出せるようにサブクエリを作る!
で。。。固まっています。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • tama_yn0815

    2019/10/10 15:30 編集

    おお!ちょっと、読んで試してみます!
    ありがとうございます!

    キャンセル

  • tama_yn0815

    2019/10/10 16:03

    @nandymak さん
    出来ました!
    ちょっと、面倒ですが、ベストアンサーにしたいので、回答にRANK()関数について投稿をお願いいたします!

    キャンセル

  • nandymak

    2019/10/10 16:27

    ご丁寧にもうも。若干内容を書き加えました。

    キャンセル

回答 2

checkベストアンサー

+1

やりたいことが理解できていませんが、
順位をつけるにはRANK()関数でできるのではないでしょうか?

PostgreSQL 9.4.5文書
9.21. ウィンドウ関数
3.5. ウィンドウ関数

いくつか種類があります。
表 9-53. 汎用ウィンドウ関数

 関数 戻り値 説明
 row_number()  bigint 1から数えたパーティション内の現在行の数
 rank() bigint ギャップを含んだ現在行の順位で、その最初の(対となる)ピアのrow
 dense_rank() bigint ギャップを含まない現在行の順位で、この関数は(対となる)ピアグループ数を計算する列

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/10 16:31

    回答ありがとうございます!
    しかも、window関数の種類についてまで解説いただき、ありがとうございます!
    本当にホトホト困っており、とても、助かりました!

    キャンセル

  • キャンセル

+1

item_use_history をwithで展開しています。

goal_result

with item_use_history as (
  SELECT *
  FROM (values
           (1,101,'2019/09/01 10:10:10'::timestamp,1,'aaa1')
          ,(2,102,'2019/09/01 10:10:10'::timestamp,1,'aaa2')
          ,(3,103,'2019/09/01 10:10:10'::timestamp,1,'aaa3')
          ,(4,101,'2019/09/01 11:10:10'::timestamp,2,'bbb1')
          ,(5,102,'2019/09/01 11:10:10'::timestamp,2,'bbb1')
          ,(6,103,'2019/09/01 11:10:10'::timestamp,2,'bbb2')
          ,(7,101,'2019/09/01 12:10:10'::timestamp,1,'ccc1')
          ,(8,102,'2019/09/01 12:10:10'::timestamp,1,'ccc2')
          ,(9,103,'2019/09/01 12:10:10'::timestamp,1,'ccc3')
        ) as item(id, user_id, datetime, item_id, value)
)

select user_id, datetime::date AS use_date, item_id, value, seq
from (
    select *
         , dense_rank() over (partition by item_id order by datetime) seq
    from item_use_history
  ) item
WHERE datetime BETWEEN '2019/09/01 00:00:00' AND '2019/09/30 23:59:59'
ORDER BY   datetime, item_id, user_id 

最終的なゴール

with item_use_history as (
  SELECT *
  FROM (values
           (1,101,'2019/09/01 10:10:10'::timestamp,1,'aaa1')
          ,(2,102,'2019/09/01 10:10:10'::timestamp,1,'aaa2')
          ,(3,103,'2019/09/01 10:10:10'::timestamp,1,'aaa3')
          ,(4,101,'2019/09/01 11:10:10'::timestamp,2,'bbb1')
          ,(5,102,'2019/09/01 11:10:10'::timestamp,2,'bbb1')
          ,(6,103,'2019/09/01 11:10:10'::timestamp,2,'bbb2')
          ,(7,101,'2019/09/01 12:10:10'::timestamp,1,'ccc1')
          ,(8,102,'2019/09/01 12:10:10'::timestamp,1,'ccc2')
          ,(9,103,'2019/09/01 12:10:10'::timestamp,1,'ccc3')
        ) as item(id, user_id, datetime, item_id, value)
)
select user_id, use_date, item_id, seq_val[1] as seq_1_value, seq_val[2] as seq_2_value
from (
  select user_id, datetime::date AS use_date, item_id
       , array_agg(value order by seq) as seq_val
  from (
      select *
           , dense_rank() over (partition by item_id order by datetime) seq
      from item_use_history
    ) item
  WHERE datetime BETWEEN '2019/09/01 00:00:00' AND '2019/09/30 23:59:59'
  group by user_id, use_date, item_id
) goal_result
order by user_id, use_date, item_id

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/10 16:25

    回答、ありがとうございます
    凄い、、、values句をちゃんと使えているSQLを始めてみました!
    とても、参考になります。
    折角、回答をいただき、しかも目的通りなのですが、質問要望箇所でRANK関数について教えて下さった方が先におり。。。ベストを付けられないのです、すみません!
    でも、とても、助かりました!こちらのSQLを参考に、実装者へ具体的な解決策を提案してあげられそうです!

    キャンセル

  • 2019/10/10 16:30

    それは構いません。
    SEQを決定した後でのwhere条件にするとか、配列に折りたたんだ後に展開しているところなんかが、SQLでの注意点です。

    キャンセル

  • 2019/10/10 16:51

    array_agg関数の使いどころをイメージしづらく、忌避していたのです。。。恥ずかしい
    この回答の感じのように、配列化して、展開する
    (CASE WHEN seq =1 THEN item_value END,CASE WHEN seq =2 THEN item_value END...)
    みたいなSQLを書く時に使えるのですね!とても参考になります!!!
    。。。むしろ、スマート(意味が分かりやすい)かつEXPLAINの計画も良い感じです。

    正直、こう言う経験値がエンジニアには必要ですね
    。。。SELECT書けますってだけだと、環境があれば誰でも書けるよなぁぁって思っています。
    ちょっとトリッキーなSELECTを用意された関数で欲しい表にまとめることが出来る力が重要だなと
    改めて、振り返らせていただけました。

    重ねてですが、本当にありがとうございます。

    P.S.> おすすめのサイト、見てみます!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.79%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • トップ
  • PostgreSQLに関する質問
  • SQLで特定カラム(複数)をGROUP BYした際に、COUNT関数で取れる数をNO GROUPの時にシーケンス的な扱いで連番を振りたい(表内重複許可/組合内重複不可_連番)