SQL問題 – skillup

今までやったSQL問題などのまとめ。定期的にやる予定です・・

自分用なのでテーブルデータとかあったりなかったりいい加減です(汗)

SQLドリル

問題1

nameとageで構成されたテーブルがあるとして、年代ごとの人数を出したいときなど(例えば未成年,20～69,70以上に分けたいときは下記のSQLで一発)

SELECT 
  CASE WHEN age < 20 THEN  '子供'        WHEN age between 20 and 69 THEN '大人'        WHEN age > 70 THEN '老人' 
       ELSE NULL END as age_seg,
  COUNT(*) ,
FROM 
Persons
GROUP BY
CASE  WHEN age < 20 THEN  '子供'       WHEN age between 20 and 69  THEN '大人'       WHEN age > 70 THEN '老人' 
      ELSE NULL END

SELECT

CASE WHEN age < 20 THEN '子供' WHEN age between 20 and 69 THEN '大人' WHEN age > 70 THEN '老人'

ELSE NULL END as age_seg,

COUNT(*) ,

FROM

Persons

GROUP BY

CASE WHEN age < 20 THEN '子供' WHEN age between 20 and 69 THEN '大人' WHEN age > 70 THEN '老人'

ELSE NULL END

問題2

pcode(郵便番号)とdistrict_name(場所の名前)があるテーブルである郵便番号(41300003)の合致度が高いもののランキングを出す

SELECT 
  pcode,
  district_name,
  CASE 
     WHEN  pcode =    '41300003'  THEN 0 
     WHEN  pcode LIKE '4130000%'  THEN 1 
     WHEN  pcode LIKE '413000%'   THEN 2 
     WHEN  pcode LIKE '41300%'    THEN 3 
     WHEN  pcode LIKE '4130%'     THEN 4 
     WHEN  pcode LIKE '413%'      THEN 5 
     WHEN  pcode LIKE '41%'       THEN 6 
     ELSE  NULL END rank
 FROM 
   postalcode 
ORDER BY 
  rank ASC 
LIMIT 3;

SELECT

pcode,

district_name,

CASE

WHEN pcode = '41300003' THEN 0

WHEN pcode LIKE '4130000%' THEN 1

WHEN pcode LIKE '413000%' THEN 2

WHEN pcode LIKE '41300%' THEN 3

WHEN pcode LIKE '4130%' THEN 4

WHEN pcode LIKE '413%' THEN 5

WHEN pcode LIKE '41%' THEN 6

ELSE NULL END rank

FROM

postalcode

ORDER BY

rank ASC

LIMIT 3;

問題3

下記テーブルがあり
cust_idごとにseqがMAXのpriceとMINのpriceの差分を求めよ
テーブル名 receipts
cust_id seq price
A 1 500
A 2 1000
A 3 700
B 5 100
B 6 50000
B 7 300
B 9 200
B 12 1000
C 10 600
C 20 100
C 45 200
C 70 50
D 3 2000

SELECT  
 T1.cust_id,  
 MAX( CASE WHEN T1.max_seq_rank = 1 THEN T1.price ELSE 0 END ) - MAX( CASE WHEN T1.min_seq_rank = 1 THEN T1.price ELSE 0 END )    
FROM
(    
SELECT 
  cust_id, 
  price, 
  RANK() OVER (PARTITION BY cust_id ORDER BY seq asc)  min_seq_rank,  
  RANK() OVER (PARTITION BY cust_id ORDER BY seq desc) max_seq_rank  
FROM 
  receipts    
) T1 
GROUP BY cust_id;

SELECT

T1.cust_id,

MAX( CASE WHEN T1.max_seq_rank = 1 THEN T1.price ELSE 0 END ) - MAX( CASE WHEN T1.min_seq_rank = 1 THEN T1.price ELSE 0 END )

FROM

(

SELECT

cust_id,

price,

RANK() OVER (PARTITION BY cust_id ORDER BY seq asc) min_seq_rank,

RANK() OVER (PARTITION BY cust_id ORDER BY seq desc) max_seq_rank

FROM

receipts

) T1

GROUP BY cust_id;

問題4
下記テーブルがあり
より古い年のデータが存在しない場合 NULL
直近の年のデータより売り上げが伸びた場合:+
直近の年のデータより売り上げが下がった場合:-
直近の年のデータと売り上げが同じ場合:=
と表記
company year sale
A 2002 52
A 2004 54
A 2005 84
B 2006 15
B 2001 95
B 2003 23
B 2008 15
C 2001 18
C 2009 32
C 2010 12

SELECT 
  company,
  year ,
  sale,
  CASE 
    WHEN (  sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING  ) )  > 0 THEN  '+'
    WHEN (  sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING  ) )  = 0  THEN '='
    WHEN (  sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING  ) )  < 0  THEN '+'
  ELSE NULL END AS var
FROM
 Sales

--さらにスマート
SELECT 
  company,
  year ,
  sale,
  CASE SIGN (  sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING  ) )  
    WHEN 1   THEN  '+' 
    WHEN -1  THEN  '-' 
    WHEN 0   THEN  '=' 
  ELSE NULL END AS var
FROM
 Sales

--サブクエリ使う
SELECT
    s2.company,
    s2.year,
    s2.sale as recent_sales,
    (   --前年度の売上はこう出す
　　　　　--出力対象が値か単行か表かをしっかり考える
        SELECT
            --こことwhere句の書き方により出力対象が値か単行か表か決まる
            s1.sale
        FROM
            Sales s1
        WHERE
            s1.year = (
                SELECT
                    MAX(s0.year)
                FROM
                    Sales s0
                WHERE
                    -- 最初は↓ここの条件が難しいので具体値を入れる
                    s0.company = s2.company
                and s0.year < s2.year
            )
        and s1.company = s2.company
    ) as prev_sale
FROM
    Sales s2
ORDER BY
    s2.company,
    s2.year

SELECT

company,

year ,

sale,

CASE

WHEN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) > 0 THEN '+'

WHEN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) = 0 THEN '='

WHEN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) < 0 THEN '+'

ELSE NULL END AS var

FROM

Sales

--さらにスマート

SELECT

company,

year ,

sale,

CASE SIGN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) )

WHEN 1 THEN '+'

WHEN -1 THEN '-'

WHEN 0 THEN '='

ELSE NULL END AS var

FROM

Sales

--サブクエリ使う

SELECT

s2.company,

s2.year,

s2.sale as recent_sales,

( --前年度の売上はこう出す

　　　　　--出力対象が値か単行か表かをしっかり考える

SELECT

--こことwhere句の書き方により出力対象が値か単行か表か決まる

s1.sale

FROM

Sales s1

WHERE

s1.year = (

SELECT

MAX(s0.year)

FROM

Sales s0

WHERE

-- 最初は↓ここの条件が難しいので具体値を入れる

s0.company = s2.company

and s0.year < s2.year

)

and s1.company = s2.company

) as prev_sale

FROM

Sales s2

ORDER BY

s2.company,

s2.year

問題5
テーブルサンプル(score)
class | member_no | point
——-+———–+——-
1 | 100 | 50
1 | 101 | 55
1 | 102 | 56
2 | 100 | 60
2 | 101 | 72
2 | 102 | 73
2 | 103 | 73
classごとに通し番号を作成

--ウィンドウ関数 
SELECT
    ROW_NUMBER () OVER( PARTITION BY class ORDER BY member_no ) AS seq,
    class,
    member_no
FROM
    score

--サブクエリ使用
SELECT
    s2.class,
    s2.member_no,
    (
        SELECT
            COUNT(*)
        FROM
            score s1
        WHERE
            s1.class = s2.class and
            s1.member_no <=  s2.member_no
    ) as seq_id
FROM
    score s2
ORDER BY
    s2.class,seq_id

--ウィンドウ関数

SELECT

ROW_NUMBER () OVER( PARTITION BY class ORDER BY member_no ) AS seq,

class,

member_no

FROM

score

--サブクエリ使用

SELECT

s2.class,

s2.member_no,

(

SELECT

COUNT(*)

FROM

score s1

WHERE

s1.class = s2.class and

s1.member_no <= s2.member_no

) as seq_id

FROM

score s2

ORDER BY

s2.class,seq_id