読者です 読者をやめる 読者になる 読者になる

技術周りのあれこれ

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

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関数で余剰計算して判定します。

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