SQL全然できない僕に教えてくれたこと①

はじめに
社内でDB勉強会が開かれたので参加して学んだことを書きます。
今回はPostgreSQLです。

t_answerテーブルのinsert_datetimeカラムに年月日時間が入っている。
　年月毎の件数を出したい。

SELECT to_char(insert_datetime,'YYYY/mm'),count(*) 
    FROM t_answer  
    GROUP BY to_char(insert_datetime, 'YYYY/mm');

to_charという関数を使って日付変換できます。
ちなみにMySQLではdate_format関数を使う。to_charは使えないので注意。

上記のデータをt_userテーブルのgender毎に分けて集計したい。

SELECT to_char(t_answer.insert_datetime,'YYYY/mm'),t_user.gender,count(*) 
    FROM t_answer  
    JOIN t_user ON t_user.id=t_answer.user_id  
    GROUP BY to_char(t_answer.insert_datetime, 'YYYY/mm'),t_user.gender;

JOINでテーブルくっつけます。

t_answerテーブルにあるcorrectの値がt：正解、f：不正解とし、得点が上位10名のユーザーを表示したい。

SELECT user_id,challenge_id,count(*)
    FROM t_answer  
    WHERE correct='t'   
    GROUP BY user_id,challenge_id  
    ORDER BY count(*) DESC LIMIT 10;

DESCで降順にして、LIMITで件数を絞ります。

ユーザ毎の平均点数上位10名を表示したい。

SELECT user_id, avg(count)  
FROM (  
    SELECT user_id,challenge_id,count(*)
    FROM t_answer  
    WHERE correct='t'   
    GROUP BY user_id,challenge_id  
    ORDER BY count(*) DESC) AS nyan  
GROUP BY user_id  
ORDER BY avg DESC LIMIT 10;

平均を出すにはavg関数を使う。
上の問題結果から表示するのでfrom内でselectして、ASで別名を付けておきます。いわゆるサブクエリってやつらしいです。
※AS nyan ＝あずにゃん（ry

t_groupにいるユーザ人数のランキング表示したい。

select RANK() over(ORDER BY count(user_id) DESC),t_group.name,count(user_id)  
    from t_group  
    JOIN r_group_member on t_group.id=r_group_member.group_id  
    GROUP BY r_group_member.group_id,t_group.name  
    ORDER BY count(r_group_member.user_id) DESC;

ランキングはRANK関数を使います。

重複するユーザーは除いた年月毎の集計を出したい。

SELECT to_char(insert_datetime,'YYYY/mm'),count(DISTINCT user_id)   
FROM t_answer  
GROUP BY to_char(insert_datetime, 'YYYY/mm') LIMIT 10;

重複する値を除くときは、DISTINCTを入れます。

ユーザ情報で
gender 1:男性
gender 2:女性
として100件表示したい。

SELECT id,name,  
CASE WHEN gender=1 THEN 'male'  
     WHEN gender=2 THEN 'female'  
ELSE 'other'  
END as nyan  
FROM t_user  
ORDER BY "id" LIMIT 100;

値を条件付きで表示したい場合はcase文を使います。

t_userのidが偶数か奇数かを表示する。

SELECT id,name,
CASE WHEN MOD(id,2)=0 THEN '偶数'  
     WHEN MOD(id,2)=1 THEN '奇数'  
ELSE 'other'  
END as nyan  
FROM t_user  
ORDER BY "id"  
LIMIT 100;

MOD関数で余剰計算して判定します。

また次回あるそうなので、続くかな。

技術周りのあれこれ

主に自分用のメモです。技術的なことや個人的にやってみたことを書いてます。

SQL全然できない僕に教えてくれたこと①