kharukaのブログ~お金と技術とキャリア~

Edu Fin~金融×教育~若いうちからお金について学ぶってだいじ!学んだテクノロジーはみんなの財産。過去、現在、将来の人生についてのブログ

MCP70-461対策(No.4)

【前提条件】

Microsoft SQL Server 2012 (SP3-CU7) (KB3205051) - 11.0.6579.0 (X64)

1.テストテーブルを作成します。

create table [REF70-461.30392-SalesPerson]
(
SalesPersonId int NOT NULL PRIMARY KEY,
Name varchar(20) NOT NULL,
City varchar(10) NOT NULL,
Country varchar(10) NOT NULL
)
go

create table [REF70-461.30392-Sale]
(
SaleId int NOT NULL PRIMARY KEY,
SalesPersonId int NOT NULL,
Amount money NOT NULL,
SaleDate datetime NOT NULL
)
go  

2.テーブルにデータを入れます。

insert into [REF70-461.30392-SalesPerson] values(1,'APerson','Tokyo','Japan')
go
insert into [REF70-461.30392-SalesPerson] values(2,'BPerson','Tokyo','Japan')
go
insert into [REF70-461.30392-SalesPerson] values(3,'CPerson','Osaka','Japan')
go
insert into [REF70-461.30392-SalesPerson] values(4,'DPerson','New York','US')
go
insert into [REF70-461.30392-SalesPerson] values(5,'EPerson','New York','US')
go

insert into [REF70-461.30392-Sale] values(1,1,1000000,'2017-03-05 10:00:00')
go
insert into [REF70-461.30392-Sale] values(2,2,3000000,'2017-03-02 11:00:00')
go
insert into [REF70-461.30392-Sale] values(3,1,1000000,'2017-03-02 12:00:00')
go
insert into [REF70-461.30392-Sale] values(4,3,2000000,'2017-03-05 9:00:00')
go
insert into [REF70-461.30392-Sale] values(5,4,1000000,'2017-03-04 8:00:00')
go
insert into [REF70-461.30392-Sale] values(6,2,5000000,'2017-03-10 8:00:00')
go
insert into [REF70-461.30392-Sale] values(7,5,3000000,'2017-03-08 11:00:00')
go
insert into [REF70-461.30392-Sale] values(8,5,1000000,'2017-03-07 10:00:00')
go
insert into [REF70-461.30392-Sale] values(9,5,2000000,'2017-03-06 9:00:00')
go
insert into [REF70-461.30392-Sale] values(10,5,1000000,'2017-03-05 11:00:00')
go
insert into [REF70-461.30392-Sale] values(11,1,1000000,'2017-03-05 10:00:00')
go
insert into [REF70-461.30392-Sale] values(12,2,3000000,'2016-03-02 11:00:00')
go
insert into [REF70-461.30392-Sale] values(13,1,1000000,'2013-03-02 12:00:00')
go
insert into [REF70-461.30392-Sale] values(14,3,2000000,'2012-03-05 9:00:00')
go
insert into [REF70-461.30392-Sale] values(15,4,1000000,'2012-03-04 8:00:00')
go
insert into [REF70-461.30392-Sale] values(16,2,5000000,'2013-03-10 8:00:00')
go
insert into [REF70-461.30392-Sale] values(17,5,3000000,'2014-03-08 11:00:00')
go
insert into [REF70-461.30392-Sale] values(18,5,1000000,'2015-03-07 10:00:00')
go
insert into [REF70-461.30392-Sale] values(19,5,2000000,'2016-03-06 9:00:00')
go
insert into [REF70-461.30392-Sale] values(20,5,1000000,'2017-03-05 11:00:00')
go

3.(営業担当者、国、都市、年別の売上合計)、(都市、国別の売上合計)、(売上合計)返すselect文をgrouping setsを使用して実行します。

SELECT [REF70-461.30392-SalesPerson].Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total
FROM [REF70-461.30392-Sale] INNER JOIN [REF70-461.30392-SalesPerson]
ON [REF70-461.30392-Sale].SalesPersonID = [REF70-461.30392-SalesPerson].SalesPersonID
GROUP BY GROUPING SETS(([REF70-461.30392-SalesPerson].Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())
go

結果

Name                 Country    City       Year        Total
-------------------- ---------- ---------- ----------- ---------------------
CPerson              Japan      Osaka      2012        2000000.00
CPerson              Japan      Osaka      2017        2000000.00
NULL                 Japan      Osaka      NULL        4000000.00
APerson              Japan      Tokyo      2013        1000000.00
APerson              Japan      Tokyo      2017        3000000.00
BPerson              Japan      Tokyo      2013        5000000.00
BPerson              Japan      Tokyo      2016        3000000.00
BPerson              Japan      Tokyo      2017        8000000.00
NULL                 Japan      Tokyo      NULL        20000000.00
NULL                 Japan      NULL       NULL        24000000.00
DPerson              US         New York   2012        1000000.00
DPerson              US         New York   2017        1000000.00
EPerson              US         New York   2014        3000000.00
EPerson              US         New York   2015        1000000.00
EPerson              US         New York   2016        2000000.00
EPerson              US         New York   2017        8000000.00
NULL                 US         New York   NULL        16000000.00
NULL                 US         NULL       NULL        16000000.00
NULL                 NULL       NULL       NULL        40000000.00

(19 行処理されました)

[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

おまけ

GROUP BY GROUPING SETS(())とgroup by rollup(xxx)は似たような使い方ができます。

group by cubeはすべての組み合わせを集計してくれます。

例:

a = 1

b=1, 2

group by cube (a, b)


a b


1 1

1 2

1 N/A

N/A N/A


参考:

d.hatena.ne.jp

qiita.com