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

Question

** 解決策反映 **

前提・実現したいこと

・使用環境：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	...

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

Comments

[workaholist]

結果例として３つ並んでいますが、どれが何ですか？
元データなのか、何らかの条件で抽出した結果なのかなど。
データから予測するしか無いですが。

[tama_yn0815]

すみません！分かり難いですよね。
ちょっと直します！

[workaholist]

countが1, 2の例となっていますが、countが1, 3, 5になった場合、
[seq_1_value][seq_3_value][seq_5_value]というふうになるのでしょうか。
つまり、結果の列数や列名は動的に変わるのでしょうか。

[tama_yn0815]

"最終的なゴール"の表は、"select:goal_result"の表から、CASE WHENで横に並べます。
seqの数は、無制限ですが、"最終的なゴール"では、足切りする想定です。
列数は、"select:goal_result"のseqカラムの値が複数想定なので、動的に変わりそうですが、
基本的には、ASで指定するので、seqカラムの値は制約外、列名はAS指定なので、静的です

[nandymak]

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

3.5. ウィンドウ関数
https://www.postgresql.jp/document/9.4/html/tutorial-window.html

[tama_yn0815]

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

[tama_yn0815]

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

[nandymak]

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

Answer 1

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

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

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

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

Comments

[tama_yn0815]

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

[sazi]

こっちもお薦め
https://lets.postgresql.jp/documents/technical/window_functions/1

Answer 2

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

Comments

[tama_yn0815]

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

[sazi]

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

[tama_yn0815]

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

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

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

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