デスぺR5午後2問1設問2の(3)の解釈と答え

依頼2を理解する必要がある

表1の依頼2

→出庫の頻度をみたい

→指定した物流拠点、期間

→棚別に集計した出庫回数が多い順に順位づけ

→上位20%を「高」、高を除く上位50%を「中」、それ以外を「低」

code:sql

WITH S1 AS (

SELECT S.拠点#, SM.棚#

FROM 出庫 S

INNER JOIN 出庫明細 SM ON S.拠点# = SM.拠点# AND S.出庫# = SM.出庫#

WHERE S.拠点# = :hv1 AND S.出庫年月日 BETWEEN :hv2 AND :hv3

)

on 拠点と出庫で、出庫と出庫明細んをinner join

更に拠点と出庫年月日の期間を指定

code:sql

WITH S1 AS (

SELECT S.拠点#, SM.棚#

FROM 出庫 S

INNER JOIN 出庫明細 SM ON S.拠点# = SM.拠点# AND S.出庫# = SM.出庫#

WHERE S.拠点# = :hv1 AND S.出庫年月日 BETWEEN :hv2 AND :hv3

), S2 AS (

SELECT キ AS 出庫回数

FROM 棚 T

LEFT JOIN S1 ON S1.拠点# = T.拠点# AND S1.棚# = T.棚#

WHERE T.拠点# = :hv1

ク

)

on 拠点#と棚#で、棚とs1をleft join

→棚を優先

→出庫回数が0である棚も載る

→そして、whereで拠点を指定

キ

キは出庫回数(asで名前づけしている)

更に次のS3で、棚#が使われていることから棚#も必要

→「棚#, 〇〇 as 出庫回数」

table:サンプルデータ

T.棚# T.拠点# S1.棚# S1.拠点#

101 1 NULL NULL

102 1 102 1

T.棚#=101のような時、出庫回数を0にしたいため、count(〇〇)にはleft join時にNULLとなるような列名である必要がある

ク

キで、countを利用していることから、group byが必要

------------------------

code:sql

WITH S1 AS (

SELECT S.拠点#, SM.棚#

FROM 出庫 S

INNER JOIN 出庫明細 SM ON S.拠点# = SM.拠点# AND S.出庫# = SM.出庫#

WHERE S.拠点# = :hv1 AND S.出庫年月日 BETWEEN :hv2 AND :hv3

), S2 AS (

SELECT T.棚#, count(S1.棚#) AS 出庫回数

FROM 棚 T

LEFT JOIN S1 ON S1.拠点# = T.拠点# AND S1.棚# = T.棚#

WHERE T.拠点# = :hv1

group by T.棚#

), S3 AS (

SELECT 棚#, RANK() OVER (ケ) AS 出庫回数順位

FROM S2

)

ケ

出庫回数順位をつけたい(つけてるasより)

出庫回数でorder by descしたら良い

---------

code:sql

WITH S1 AS (

SELECT S.拠点#, SM.棚#

FROM 出庫 S

INNER JOIN 出庫明細 SM ON S.拠点# = SM.拠点# AND S.出庫# = SM.出庫#

WHERE S.拠点# = :hv1 AND S.出庫年月日 BETWEEN :hv2 AND :hv3

), S2 AS (

SELECT T.棚#, count(S1.棚#) AS 出庫回数

FROM 棚 T

LEFT JOIN S1 ON S1.拠点# = T.拠点# AND S1.棚# = T.棚#

WHERE T.拠点# = :hv1

group by T.棚#

), S3 AS (

SELECT 棚#, RANK() OVER (order by 出庫回数 desc) AS 出庫回数順位

FROM S2

)

SELECT 棚#,

CASE

WHEN (100 * コ OVER()) <= 20 THEN '高'

WHEN (100 * コ OVER()) <= 50 THEN '中'

ELSE '低'

END AS 出庫頻度区分

FROM S3

コ

FROM S3としていることから列の選択肢は「棚#」「出庫回数順位」の2つしかない

欲しいやつが出庫回数順位の上位N%とかなんだから

「出庫回数順位」となる

全体を100%とした時の上位N%であるため少し加工が必要

全体が10個の時、順位が1位は上位10%以内ということになる

100 * 1だと100になってしまいおかしくなる

over()を利用していることから count(*) が使える