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

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

新規登録して質問してみよう
ただいま回答率
85.50%
COUNT

COUNT は、広く使用されているSQLの関数です。COUNT関数は、行数、もしくは配列のエンティティの数をカウントします。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

1549閲覧

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

tama_yn0815

総合スコア143

COUNT

COUNT は、広く使用されているSQLの関数です。COUNT関数は、行数、もしくは配列のエンティティの数をカウントします。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/10/10 05:15

編集2019/10/10 07:09

** 解決策反映 **

前提・実現したいこと

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

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

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

iduser_iddatetimeitem_idvalue
11012019/09/01 10:10:101aaa1
21022019/09/01 10:10:101aaa2
31032019/09/01 10:10:101aaa3
41012019/09/01 11:10:102bbb1
51022019/09/01 11:10:102bbb1
61032019/09/01 11:10:102bbb2
71012019/09/01 12:10:101ccc1
81022019/09/01 12:10:101ccc2
91032019/09/01 12:10:101ccc3

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

iduser_iddatetimeitem_idvalueseq
11012019/09/01 10:10:101aaa11
21022019/09/01 10:10:101aaa21
31032019/09/01 10:10:101aaa31
41012019/09/01 11:10:102bbb11
51022019/09/01 11:10:102bbb11
61032019/09/01 11:10:102bbb21
71012019/09/01 12:10:101ccc12
81022019/09/01 12:10:101ccc22
91032019/09/01 12:10:101ccc32

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

user_iduse_dateitem_idcount(item_id)
1012019/09/0112
1022019/09/0112
1032019/09/0112
1012019/09/0121
1022019/09/0121
1032019/09/0121
### 直面している課題
SQLが思いつかない。。。知恵を貸してください!

該当のソースコード

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

SQL

1WITH item_count AS ( 2 SELECT *, RANK() OVER (PARTITION BY i.use_date, i.user_id, i.item_id ORDER BY i.datetime) AS seq 3 FROM ( 4 SELECT 5 item.*, 6 TO_CHAR(item.datetime::TIMESTAMP, 'YYYY/MM/DD') AS use_date 7 FROM 8 item_use_history AS item 9 WHERE 10 item.datetime BETWEEN ('2019/09/01 00:00:00' AND '2019/09/30 23:59:59') 11 ) AS i 12 WHERE TRUE =TRUE 13 --GROUP BY 14 --ORDER BY 15) 16SELECT * 17FROM item_count 18WHERE TRUE = TRUE 19--GROUP BY 20--ORDER BY

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

SQL

1SELECT 2 item.user_id, 3 TO_CHAR(item.datetime::TIMESTAMP, 'YYYY/MM/DD') AS use_date, 4 item.item_id, 5 count(item_id) 6FROM 7 item_use_history AS item 8WHERE 9 i.datetime BETWEEN ('2019/09/01 00:00:00' AND '2019/09/30 23:59:59') 10GROUP BY 11 item.user_id, use_date, item.item_id 12ORDER BY 13 use_dateASC, 14 item.user_id ASC, 15 item.item_id ASC,

調べている情報

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

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

最終的なゴール

こんな表にしたい!!

user_iduse_dateitem_idseq_1seq_1_valueseq_2seq_2_value...
1012019/09/0111aaa12ccc1...
1022019/09/0111aaa22ccc1...
1032019/09/0111aaa32ccc1...
1012019/09/0121bbb2NULLNULL...
1022019/09/0121bbb2NULLNULL...
1032019/09/0121bbb2NULLNULL...

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

workaholist

2019/10/10 05:25

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

2019/10/10 05:26

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

2019/10/10 06:02

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

2019/10/10 06:08 編集

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

2019/10/10 06:30 編集

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

2019/10/10 07:03

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

2019/10/10 07:27

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

回答2

0

ベストアンサー

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

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

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

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

投稿2019/10/10 07:26

nandymak

総合スコア799

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tama_yn0815

2019/10/10 07:31

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

0

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

goal_result

SQL

1with item_use_history as ( 2 SELECT * 3 FROM (values 4 (1,101,'2019/09/01 10:10:10'::timestamp,1,'aaa1') 5 ,(2,102,'2019/09/01 10:10:10'::timestamp,1,'aaa2') 6 ,(3,103,'2019/09/01 10:10:10'::timestamp,1,'aaa3') 7 ,(4,101,'2019/09/01 11:10:10'::timestamp,2,'bbb1') 8 ,(5,102,'2019/09/01 11:10:10'::timestamp,2,'bbb1') 9 ,(6,103,'2019/09/01 11:10:10'::timestamp,2,'bbb2') 10 ,(7,101,'2019/09/01 12:10:10'::timestamp,1,'ccc1') 11 ,(8,102,'2019/09/01 12:10:10'::timestamp,1,'ccc2') 12 ,(9,103,'2019/09/01 12:10:10'::timestamp,1,'ccc3') 13 ) as item(id, user_id, datetime, item_id, value) 14) 15 16select user_id, datetime::date AS use_date, item_id, value, seq 17from ( 18 select * 19 , dense_rank() over (partition by item_id order by datetime) seq 20 from item_use_history 21 ) item 22WHERE datetime BETWEEN '2019/09/01 00:00:00' AND '2019/09/30 23:59:59' 23ORDER BY datetime, item_id, user_id 24

最終的なゴール

SQL

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

投稿2019/10/10 07:13

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

tama_yn0815

2019/10/10 07:25

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

2019/10/10 07:30

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

2019/10/10 07: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.> おすすめのサイト、見てみます!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問