導入

blogさぼってましたが、IT関連は自身の備忘のために記録したいと思います。

★テーマ

GROUP BY で集約操作したグループに特定のフラグを立てる方法について記録します。
より一般化した表現をすると、集合の性質を調べる方法とでもいいましょうか。

個人で実装していたとき、ふと思いついたんですが、Webを見ると既出のようですね。

目次・流れ

No	タイトル
1	概要/事例/目的
2	コード/実行結果
3	解説・補足

＜前提＞
・データ操作言語(※DML/Data Manipulation Language)について概要を理解していること。
※SELECT/INSERT/UPDATE/DELETEなどテーブルに対するデータの取得・追加・更新・削除を行うコマンド。
・事例等は極力シンプルな記載にしています。

＜実行環境＞
DBのミドルウェア：PostgreSQL 10.6, compiled by Visual C++ build 1800, 64-bit
※SELECT version();で取得
※Oracle/SQLServerでも有効な記事です。

概要/事例/目的

社内で利用するアンケートアプリを作成した際、アンケート毎に下記を表示したいとのことになりました。

①全体の回答数
②自分が回答済みか否かのフラグ【0:未回答/1:回答済】をステータスとして表示したい。

本アンケートには、証券アナリストの方がヒアリングをかけたい質問などを設定します。

「アンケート毎に」とあるのでGROUP BY句の出番というのは容易に想像がつきます。

集合論的なイメージは下記の通り。
各グループに集約操作を行いつつ、各グループの性質・フラグを設定します。

コード/実行結果

テーブル定義

アンケート結果を記録するテーブルです。

CREATE TABLE t_analyst_answer( 
  QUESTION_ID CHAR (8) NOT NULL
  , REG_USER varchar (50) NOT NULL
  , ANSWER CHAR (2) NOT NULL
  , REG_DATE CHAR (8)
  , REG_TIME CHAR (4)
  , PRIMARY KEY (QUESTION_ID, REG_USER)
) ;

データサンプル

INSERTINTO t_analyst_answerVALUES ('Q0000001', '芦田マラ', 'C1', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'TOM', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'ANDY', 'C1', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'MIKE', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000001', 'BILL', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'TOM', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'ANDY', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'MIKE', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000002', 'BILL', 'C2', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000003', '芦田マラ', 'C1', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000003', 'MIKE', 'C3', '20190408', '1200');
INSERTINTO t_analyst_answerVALUES ('Q0000003', 'BILL', 'C3', '20190408', '1200');

上記のように、質問ID(QUESTION_ID)と登録ユーザ(REG_USER)をキーに、アンケート結果を記録するテーブルを設計しました。
回答(ANSWER)には選択肢(choice)のコードを適当に設定しています。

コード

SELECT
QUESTION_ID AS 質問ID
, COUNT(*) AS 件数

, COUNT(
    CASE WHEN REG_USER = '芦田マラ' THEN 1 ELSE NULL END
    ) 回答済FLG

FROM
  t_analyst_answer 
GROUP BY
  QUESTION_ID 
ORDER BY
  QUESTION_ID;

実行結果

解説

＜操作＞

・質問IDごとに件数を集計しています。
・対象の質問IDの集合に対し、レコードを1件ずつCASE文で走査してます。具体的には

①REG_USER = '芦田マラ'がTRUEのとき、COUNT関数でTRUEの件数が集計されます。
②COUNT(NULL)の際、0を返すという性質を利用してます。

・'芦田マラ'を社員IDなどにすれば、自分が回答済みかどうかということがわかります。

/*再掲*/
SELECT
QUESTION_ID AS 質問ID
, COUNT(*) AS 件数
/*●OK*/
, COUNT(
    CASE WHEN REG_USER = '芦田マラ' THEN 1 ELSE NULL END
    ) 回答済FLG
/*COUNTという集約関数の引数に対し、REG_USERを指定*/

/*●NG*/
/*,CASE WHEN REG_USER = '芦田マラ' THEN 1 ELSE 0 END
GROUP BY句に存在しない列名をSELECT句に指定するとエラーになるよ！
*/

FROM
  t_analyst_answer 
GROUP BY
  QUESTION_ID 
ORDER BY
  QUESTION_ID;

＜補足＞

GROUP BY句に存在しない列名をSELECT句に指定するとエラーになりますが、
集約関数の引数に対しては、GROUP BYにない列名を設定できます。
そのため、COUNTという集約関数の引数に対し、REG_USERを指定しています。

SQLのGROUP BY句の処理を理解する - ぱと隊長日誌

以上。

某4人兄弟バンドに関して宣伝させてください。

2019年内に私の好きなバンド【SaToMansion】が解散の危機を迎えています！

マジで勘弁して欲しいけど。彼らも本気ということです。

彼らの曲を聴いて下さい。マジで掛値なしにかっこいい。

そしてYou Tubeのチャンネル登録をお願いします!

我らが百獣の王、
武井壮さんからの指令で

サトマンTube、年内に
チャンネル登録数10000人達成！
出来なければ解散という事になりました！

詳しくはこちらの動画を↓https://t.co/VI0zRHlYPY

昨日1000人を突破しましたが、あと9000人…皆さまのご協力が必要です！
ぜひ拡散の程お願いします！🙇‍♂️ pic.twitter.com/ufGqkWXX2K
— SaToMansion【公式】 (@SaToMansion_off) 2019年3月13日