# 2021年1月SQL練習課題回答 | LittleVoice-g-string

LittleVoice-g-string

苦しむために生きないであなた自身を愛してくれ

2021年1月SQL練習課題回答

Posted on Edited on In
Symbols count in article: 5.9k Reading time ≈ 5 mins.

説明：SQLの操作はpsycopg2を使って、Python上での操作を行いました。

1.2021年1月12日

勉強したSQL構文:

OVER(PARTITION BY column1 ORDER BY column2)

①データ挿入

%%sql
CREATE TABLE T0104(
    ID INT,
    NAME VARCHAR(10),
    NUM INT)
INSERT INTO T0104 VALUES(1,'A',1);
INSERT INTO T0104 VALUES(2,'A',2);
INSERT INTO T0104 VALUES(3,'A',6);
INSERT INTO T0104 VALUES(4,'A',4);
INSERT INTO T0104 VALUES(5,'A',3);
INSERT INTO T0104 VALUES(6,'B',2);
INSERT INTO T0104 VALUES(7,'B',8);
INSERT INTO T0104 VALUES(8,'B',2);
②条件を設定し、データを絞り込む 
%%sql

select A.id,A.name,A.num from(
select T.ID,T.NAME,T.NUM,
sum(NUM) OVER(PARTITION BY NAME ORDER BY ID)*1.0 a,
sum(NUM) OVER(PARTITION BY NAME ORDER BY ID) *1.0 b,
sum(NUM) OVER(PARTITION BY NAME)*1.0 c from T0104 as T
) A
where A.a/A.c > 0.6;

2.2021年1月13日

勉強したSQL構文:

1.column1をもとに分割し、column2をもとにそれぞれ順番そろえて、順位を新しい列につけて行く

row_number() over(partion column1 order by column2)

2.coalesce関数

coalesce(A,B,C…):()の中に一つの非NULLの値が出力される
参考記事:CASE式で条件分岐をSQL文に任せる

3.条件分岐構文:検索case式

case 
    when column1 = '' then 結果1
    when column1 = '' then 結果2
    else ●●
END

①まず条件分岐で正負の検索結果をつけていき、コードごとに順番並ぶ

%%sql
SELECT *,
case when 金額相違値 >= 0 then '正'
    when 金額相違値 < 0 then '負'
        else '' end as 増長方向,
        row_number() over(partition by コード order by 日付) rn from T0113
　 ②上記結果をまるごとuにし、a,bという名をつけて、同じコード & bのrn列はaのrnより値が低い & 増長方向が変更した節点という三つ条件が満足した行を絞り込み、降順した第一行目を抽出する 
%%sql
select b.rn from u b
where b.コード = a.コード
and b.rn<a.rn
and b.増長方向 != a.増長方向
order by b.rn desc limit 1

③上記条件を満足されない行に関しては、null値になるので、coalesce関数でnull値を0に変更し、aのrn行と引き算を行い、連続増長日数という新しい一列にする。

select 日付,コード,金額,金額相違値,増長方向,
a.rn - coalesce((select b.rn from u b
                where b.コード = a.コード
                and b.rn<a.rn
                and b.増長方向 != a.増長方向
                order by b.rn desc limit 1),0) "連続増長日数" from u a

④完成版と結果は以下の通り 

%%sql
with u as
(SELECT *,
case when 金額相違値 >= 0 then '正'
    when 金額相違値 < 0 then '負'
        else '' end as 増長方向,
        row_number() over(partition by コード order by 日付) rn 
 from T0113)
select 日付,コード,金額,金額相違値,増長方向,
    a.rn - coalesce((select b.rn from u b
                    where b.コード = a.コード
                    and b.rn<a.rn
                    and b.増長方向 != a.増長方向
                    order by b.rn desc limit 1),0) "連続増長日数" from u a

3.2021年1月16日

select 商品,日付,sum(単価*個数)/sum(個数) as 平均単価 
from (select *, 
dense_rank() OVER(PARTITION BY 商品 ORDER BY 日付) from T0116)as a
where dense_rank = 1
group by 商品,日付;

4.2021年1月17日

勉強したSQL構文:

CONCATを使って、数字型と文字型の結合異なるデータ型を結合したい

参考記事：SQL 文字列結合メモ

①まずover()関数を使ってコードごとにグループ分けを行い、順位をつける 

select *,row_number() over(PARTITION by L order by x) as m from T0117
②つけた順位をもとに、さらにグループ分けを行う 
select *,x-row_number() over(PARTITION by L order by x) as m from T0117
③お題の求めた通り、グループごとに最小値、最大値の間に'ー'をつける 
concat((min(x),'-',max(x)))

④まとめる

select l,concat(min(x),'-',max(x)) as y from 
(select *,x-row_number() over(PARTITION by L order by x) as m from T0117
) as a
group by l,m;

5.2021年1月21日

勉強したSQL構文:

DATEから年月のみを取得

参考記事日付データを任意のフィールドで切り捨てる関数(DATE_TRUNC関数)

①後ほど条件判定を行えるため、DATEから年月のみを取得

select *,to_char(mon,'YYYY-MM') as MONTH from T0121;
②同じユーザー、同じ月の行を選び出し、金額/カウントで結果得る 
with u as
(select *,to_char(mon,'YYYY-MM') as MONTH from T0121)
select id,name,month,state,amount/(select count(*) from u as b 
                                 where a.month=b.month
                                 and b.name = a.name) as number
from u as a;

6.2021年1月25日

①データ型書式設定関数

postgresql:to_char([元列名], ‘YYYY-MM-DD HH24:MI’)
mysqlなら:date_format([元列名], ‘%Y-%m-%d %H:%i’)

②join結合テーブル

③with as使いすぎるとメモリが食われやすいので、要注意

①データの型をお題通りに揃える

select *,to_char(TransTime,'YYYY-MM-DD HH24:MI') as TransTimed from T0125;
②後ほどのグループ分けのためopcodeごとに順位を付けていく 
with u as
(select *,to_char(TransTime,'YYYY-MM-DD HH24:MI') as TransTimed from T0125)
select *,row_number() over(PARTITION BY opcode order by transtimed) as rn from u;
③次の行を一個上に持ち上げいき、それぞれのtranstimeをstarttimeとendtimeに設定する。transtypeは後ほど文字列の結合をするため、新しい列を作る。 
with uu as
(with u as
(select *,to_char(TransTime,'YYYY-MM-DD HH24:MI') as TransTimed from T0125)
select *,row_number() over(PARTITION BY opcode order by transtimed) as rn from u)
select a.transtype as transtype_1,b.transtype as transtype_2,a.oprseq,a.opcode,a.rn,
a.transtimed as starttime,b.transtimed as endtime from uu as a
    left join uu as b
        on b.opcode=a.opcode
            and b.rn = a.rn+1;
④奇数列のみ残り(where a.rn %2!=0)、transtypeの２列を結合し、お題が求める列のみを抽出する 
with result as

(with uu as

(with u as
(select *,to_char(TransTime,'YYYY-MM-DD HH24:MI') as TransTimed from T0125)

select *,row_number() over(PARTITION BY opcode order by transtimed) as rn from u)

select a.transtype as transtype_1,b.transtype as transtype_2,a.oprseq,a.opcode,a.rn,
 a.transtimed as starttime,b.transtimed as endtime from uu as a
     left join uu as b
         on b.opcode=a.opcode
         and b.rn = a.rn+1
where a.rn %2!=0)

select concat(result.transtype_1,'-',result.transtype_2) as transtype,result.oprseq,result.opcode,result.starttime,result.endtime 
from result;

7.2021年1月26日

①テーブルを作成し、データを挿入する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table T0126A
(id serial,
spmc VARCHAR(100),
分配量 NUMERIC(18,4));

insert into T0126A(spmc,分配量) values
('A',80),
('B',100);

create table T0126B
(id serial,
spmc VARCHAR(100),
購入量 NUMERIC(18,4));

insert into T0126B(spmc,購入量) values
('A',20),
('A',50),
('A',40),
('A',30),
('B',120),
('B',80),
('B',100);

ポイント1:serial

  • テーブルの列に一意の識別子を設定する簡便な表記法
  • integer列(整数列)が作成され、その列のデフォルト値が連番整数を振り当てる
  • PostgreSQL7.3及び以降のバージョンでは、UNIQUEまたはPRIMARY KEYにしたい場合、他のすべてのデータ型と同様に指定する必要がある
  • mysqlならAUTO_INCREMENT,OracleならSEQUENCE(PostgreSQLも)で連番を採番できる
  • 書き方
CREATE TABLE tablename(
    columnname SERIAL);

参考記事:シリアルデータ型

②spmcごとに累計購入量を計算する

1
2
3
4
select *, (select sum(購入量) from T0126B as B
    where B.spmc=A.spmc
          and B.id<= A.id) as sum_
    from T0126B as A
③T0126Bの累計購入量がT0126Aの総分配量より少ない場合、それまで毎回の購入量が全部分配されたと考えられる。それに反した場合、累計購入量とその回の購入量の差が総分配量より少ない場合、その回では一部の購入量が分配されたと考えられる。そうではない場合、余った部分は在庫量になる 
1
2
3
4
5
6
7
8
9
10
11
12
with T as(
    select *, (select sum(購入量) from T0126B as B
    where B.spmc=A.spmc
          and B.id<= A.id) as sum_
    from T0126B as A)
select t.spmc,t.購入量,
(case when T.sum_ <= U.分配量 then T.購入量 else 
     (case when (T.sum_-T.購入量)<U.分配量 then U.分配量-(T.sum_-T.購入量) else 0 end)
end) as CW
from T
inner join T0126A as U
    on t.spmc=U.spmc

④列名をお題通りに変更する

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with T as
(select *, (select sum(購入量) from T0126B as B
    where B.spmc=A.spmc
          and B.id<= A.id) as sum_
    from T0126B as A),
V as
(select t.spmc,t.購入量,
(case when T.sum_ <= U.分配量 then T.購入量 else 
     (case when (T.sum_-T.購入量)<U.分配量 then U.分配量-(T.sum_-T.購入量) else 0 end)
end) as CW
from T
inner join T0126A as U
    on t.spmc=U.spmc)
select spmc,V.購入量,V.cw as 今回使用量,(V.購入量-cw) as 在庫量 from V;

Welcome to my other publishing channels