Programming

進階MSSQL語法

(參考MSSQL)

Context

我們是個中文教學的網絡平台，透過跟不同的課程提供者合作來盈利。
擁有以下的資料表，來記錄平台的狀況。

為求簡化，我們不會列出所有的表跟欄位。
其中有些表可能違反了正規化。

  • Table: SessionInfo
Column Description
Id 課程編號
Browser 使用的瀏覽器 (Chrome, Firefox, Safari, …)
ServiceProvider 課程服務的提供者 (TutorChinese, ChineseTalker, ChineseCamp, …)
Country 連線的國家 (Japan, Korea, Singapore, …)
Cost 課程花費 (單位為連線國家的貨幣)
TeachingFee 給提供者的教學費用 (單位為連線國家的貨幣)
ConnectionTime 連線時間 (以秒記)
UserId 使用者編號
Type 課程類型 (Listening, Speaking, Reading, Writing)
Status 課程狀態 (Started, Cancelled, Done)
Date 連線日期 (UTC 時間)
  • Table: ExchangeRate
Column Description
Id 貨幣換算編號
Country 貨幣換算的國家 (Japan, Korea, Singapore, Thailand, US, …)
MonthKey 貨幣換算的時間 (格式: 202301)
Rate 匯率

Preparation

  • Table: SessionInfo

Create Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE
  SessionInfo (
    Id INT IDENTITY (1, 1) PRIMARY KEY,
    Browser nvarchar (50) NOT NULL,
    ServiceProvider nvarchar (20) NOT NULL,
    Country nvarchar (50) NOT NULL,
    Cost DECIMAL(18, 6) NOT NULL,
    TeachingFee DECIMAL(18, 6) NOT NULL,
    ConnectionTime INT NOT NULL,
    UserId INT NOT NULL,
    Type VARCHAR(20) NOT NULL,
    Status VARCHAR(20) NOT NULL,
    DATE datetime NOT NULL,
  );

Insert Data

  • 利用 PHP，產生隨機資料的程式碼
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php
$browsers = ['Chrome', 'Firefox', 'Safari'];
$serviceProviders = ['TutorChinese', 'ChineseTalker', 'ChineseCamp'];
$countries = ['Japan', 'Korea', 'Singapore'];
$types = ['Listening', 'Speaking', 'Reading', 'Writing'];
$statuses = ['Starting', 'Cancelled', 'Done'];

$result = 'INSERT INTO SessionInfo (Browser, ServiceProvider, Country, Cost, TeachingFee, ConnectionTime, UserId, Type, Status, DATE)
VALUES ';
for ($i = 1; $i <= 1000; $i++) {
	shuffle($browsers);
	shuffle($serviceProviders);
	shuffle($countries);
	$cost = rand(8443, 243427);
	$fee = (rand(300, 800)) * 0.001 * $cost;
	$connectionTime = rand(3550, 11000);
	$userId = rand(1,1000);
	shuffle($types);
	shuffle($statuses);
	$dateInt2022= mt_rand(1640995200 ,1672444800);
	$date = date("Y-m-d H:i:s",$dateInt2022);

	$result .= "('$browsers[0]', '$serviceProviders[0]', '$countries[0]', $cost, $fee, $connectionTime, $userId, '$types[0]', '$statuses[0]', '$date'),\n";
}

$result = substr($result, 0, -2) . ';';

echo($result);
  • 產生的範例
1
2
3
INSERT INTO SessionInfo (Browser, ServiceProvider, Country, Cost, TeachingFee, ConnectionTime, UserId, Type, Status, DATE)
VALUES ('Chrome', 'TutorChinese', 'Japan', 8443, 6332, 3600, 1, 'Speaking', 'Done', '2022-01-15 09:00:00'),
('Chrome', 'TutorChinese', 'Korea', 81709, 61283, 3610, 2, 'Speaking', 'Done', '2022-01-15 09:00:00');

(Note.)

利用上述程式碼產生的資料，
主要盈利的分佈會是 Singapore > Japan >>> Korea

  • Table: ExchangeRate

Create Table

1
2
3
4
5
6
7
CREATE TABLE
  ExchangeRate (
    Id INT IDENTITY (1, 1) PRIMARY KEY,
    Country nvarchar (50) NOT NULL,
    Rate DECIMAL(10, 6) NOT NULL,
    MonthKey datetime NOT NULL,
  );

Insert Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
INSERT INTO ExchangeRate (Country, Rate, MonthKey)
VALUES ('Japan', 0.2408, CONVERT(DATETIME,'20220101', 112)),
('Japan', 0.242, CONVERT(DATETIME,'20220201', 112)),
('Japan', 0.2443, CONVERT(DATETIME,'20220301', 112)),
('Japan', 0.2341, CONVERT(DATETIME,'20220401', 112)),
('Japan', 0.2264, CONVERT(DATETIME,'20220501', 112)),
('Japan', 0.224, CONVERT(DATETIME,'20220601', 112)),
('Japan', 0.2205, CONVERT(DATETIME,'20220701', 112)),
('Japan', 0.2292, CONVERT(DATETIME,'20220801', 112)),
('Japan', 0.2181, CONVERT(DATETIME,'20220901', 112)),
('Japan', 0.2198, CONVERT(DATETIME,'20221001', 112)),
('Japan', 0.2173, CONVERT(DATETIME,'20221101', 112)),
('Japan', 0.2263, CONVERT(DATETIME,'20221201', 112)),
('Korea', 0.0233, CONVERT(DATETIME,'20220101', 112)),
('Korea', 0.0231, CONVERT(DATETIME,'20220201', 112)),
('Korea', 0.0233, CONVERT(DATETIME,'20220301', 112)),
('Korea', 0.0235, CONVERT(DATETIME,'20220401', 112)),
('Korea', 0.0233, CONVERT(DATETIME,'20220501', 112)),
('Korea', 0.0235, CONVERT(DATETIME,'20220601', 112)),
('Korea', 0.023, CONVERT(DATETIME,'20220701', 112)),
('Korea', 0.0231, CONVERT(DATETIME,'20220801', 112)),
('Korea', 0.0225, CONVERT(DATETIME,'20220901', 112)),
('Korea', 0.0221, CONVERT(DATETIME,'20221001', 112)),
('Korea', 0.0227, CONVERT(DATETIME,'20221101', 112)),
('Korea', 0.0235, CONVERT(DATETIME,'20221201', 112)),
('Singapore', 20.552, CONVERT(DATETIME,'20220101', 112)),
('Singapore', 20.5859, CONVERT(DATETIME,'20220201', 112)),
('Singapore', 20.6607, CONVERT(DATETIME,'20220301', 112)),
('Singapore', 21.1392, CONVERT(DATETIME,'20220401', 112)),
('Singapore', 21.2932, CONVERT(DATETIME,'20220501', 112)),
('Singapore', 21.2106, CONVERT(DATETIME,'20220601', 112)),
('Singapore', 21.3519, CONVERT(DATETIME,'20220701', 112)),
('Singapore', 21.9154, CONVERT(DATETIME,'20220801', 112)),
('Singapore', 21.8025, CONVERT(DATETIME,'20220901', 112)),
('Singapore', 22.1604, CONVERT(DATETIME,'20221001', 112)),
('Singapore', 22.711, CONVERT(DATETIME,'20221101', 112)),
('Singapore', 22.5993, CONVERT(DATETIME,'20221201', 112));

Menu

Annual Report By Country

Quarter Type Country Count QAU Volume(TWD) Fee(TWD) FeePercentage(TWD)
Q1 Listening Japan 30 12 73000 51100 70%
…

Volume Over 50k Users By Country

Quarter Country Count
Q1 Japan 112
…

Connection Time Over 2 Hours Percentage By Type

Quarter Type Percentage
Q1 Speaking 24%
…

Favorite Service Provider By Browser

Browser ServiceProvider MaxCount
Chrome TutorChinese 30
…

Done Rate By Country

Type Country DoneRate
Speaking Japan 75%
…

Japan Consumption Quartile

25th 50th 75th Volume
2353 6794 8443 250
…

Annual Report By Country

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
;

WITH SessionInfoV2 AS (
SELECT
	si.Browser ,
	si.ServiceProvider ,
	si.Country ,
	si.ConnectionTime ,
	si.UserId ,
	si.[Type] ,
	si.Status ,
	CONCAT('Q', DATEPART(QUARTER, si.[DATE])) AS Quarter,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, si.[DATE]), 0) AS Monthkey,
	si.Cost ,
	si.TeachingFee
FROM
	SessionInfo si WITH (nolock)
)
SELECT
	siv2.Quarter,
	siv2.[Type] ,
	siv2.Country,
	Count(1) AS Count,
	Count(DISTINCT(siv2.UserId)) AS QAU,
	Sum(siv2.Cost * er.Rate) AS 'Volume(TWD)',
	Sum(siv2.TeachingFee * er.Rate) AS 'Fee(TWD)',
	CONCAT(
		CONVERT (Decimal(10, 2),
		Round(
			Sum(siv2.TeachingFee * er.Rate)/ Sum(siv2.Cost * er.Rate), 4
		)* 100),
	'%') AS 'FeePercentage(TWD)'
FROM
	SessionInfoV2 AS siv2 WITH (nolock)
JOIN ExchangeRate AS er ON
	siv2.MonthKey = er.MonthKey
	AND siv2.Country = er.Country
WHERE
	siv2.Status = 'Done'
GROUP BY
	siv2.Quarter,
	siv2.[Type],
	siv2.Country;

Volume Over 50k Users By Country

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
;

WITH SessionInfoV2 AS (
SELECT
	si.Browser ,
	si.ServiceProvider ,
	si.Country ,
	si.ConnectionTime ,
	si.UserId ,
	si.[Type] ,
	si.Status ,
	CONCAT('Q', DATEPART(QUARTER, si.[DATE])) AS Quarter,
	DATEADD(MONTH, DATEDIFF(MONTH, 0, si.[DATE]), 0) AS Monthkey,
	si.Cost ,
	si.TeachingFee
FROM
	SessionInfo si WITH (nolock)
),
SessionInfoV3 AS (
SELECT
		siv2.Quarter,
		siv2.Country,
		siv2.UserId,
		Sum(siv2.Cost * er.Rate) AS 'Volume'
FROM
	SessionInfoV2 siv2 WITH (nolock)
JOIN ExchangeRate AS er ON
	siv2.MonthKey = er.MonthKey
	AND siv2.Country = er.Country
WHERE
	siv2.Status = 'Done'
GROUP BY
	siv2.Quarter,
	siv2.Country,
	siv2.UserId
)
SELECT
	siv3.Quarter,
	siv3.Country,
	Count(DISTINCT(siv3.UserId)) AS Count
FROM
	SessionInfoV3 AS siv3 WITH (nolock)
WHERE
	siv3.Volume > 50000
GROUP BY
	siv3.Quarter,
	siv3.Country;

(Back to Menu)

Connection Time Over 2 Hours Percentage By Type

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
;

WITH SessionInfoV2 AS (
SELECT
	si.ConnectionTime ,
	si.[Type] ,
	si.Status ,
	CONCAT('Q', DATEPART(QUARTER, si.[DATE])) AS Quarter
FROM
	SessionInfo si WITH (nolock)
),
Summary AS (
SELECT
	siv2.Quarter,
	siv2.[Type],
	Count(1) AS Count
FROM
	SessionInfoV2 AS siv2 WITH (nolock)
WHERE
	siv2.Status = 'Done'
GROUP BY
	siv2.Quarter,
	siv2.[Type]
),
Over2Hour AS (
SELECT
	siv2.Quarter,
	siv2.[Type],
	Count(1) AS Count
FROM
	SessionInfoV2 AS siv2 WITH (nolock)
WHERE
	siv2.ConnectionTime >= 7200
	AND siv2.Status = 'Done'
GROUP BY
	siv2.Quarter,
	siv2.[Type]
)
SELECT
	sm.Quarter,
	sm.[Type],
	CONCAT (
		CONVERT(
			DECIMAL(10, 2),
			(
				CONVERT(DECIMAL(10, 2), ISNULL (o2.Count, 0)) / CONVERT(DECIMAL(10, 2), sm.Count)
			) * 100
		),
		'%'
	) AS Percentage
FROM
	Summary AS sm
LEFT JOIN Over2Hour AS o2 WITH (nolock)
ON
	sm.Quarter = o2.Quarter
	AND sm.[Type] = o2.[Type]
ORDER BY
	sm.Quarter,
	sm.[Type];

(Back to Menu)

Favorite Service Provider By Browser

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
;

WITH Summary AS (
SELECT
	si.ServiceProvider ,
	si.Browser,
	Count(DISTINCT (si.UserId)) AS Count
FROM
	SessionInfo si WITH (nolock)
WHERE
	si.Status = 'Done'
GROUP BY
	si.ServiceProvider ,
	si.Browser
)
SELECT
	s.ServiceProvider,
	s.Browser,
	s2.MaxCount
FROM
	Summary AS s WITH (nolock)
INNER JOIN (
	SELECT
		s1.ServiceProvider,
		Max(s1.Count) AS MaxCount
	FROM
		Summary AS s1 WITH (nolock)
	GROUP BY
		s1.ServiceProvider) s2
ON
	s.ServiceProvider = s2.ServiceProvider
	AND s.Count = s2.MaxCount;

(Back to Menu)

Done Rate By Country

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
;

WITH SessionInfoV2 AS (
SELECT
	si.[Type] ,
	si.Country ,
	si.Status,
	Count(si.Id) AS Count
FROM
	SessionInfo si WITH (nolock)
GROUP BY
	si.[Type],
	si.Country,
	si.Status
),
Summary AS (
SELECT
	siv2.[Type] ,
	siv2.Country ,
	Sum(siv2.Count) AS Count
FROM
	SessionInfoV2 siv2 WITH (nolock)
GROUP BY
	siv2.[Type],
	siv2.Country
),
Done AS (
SELECT
	siv2.[Type] ,
	siv2.Country ,
	siv2.Count
FROM
	SessionInfoV2 siv2 WITH (nolock)
WHERE
	siv2.Status = 'Done'
)
SELECT
	s.[Type],
	s.Country,
	CONCAT(
		CONVERT(
			DECIMAL(10,2),
			(
				CONVERT(DECIMAL(10,2),ISNULL (d.Count,0)) / CONVERT(DECIMAL(10,2),s.Count)
			) * 100
		), '%') AS DoneRate
FROM
	Summary AS s WITH (nolock)
LEFT JOIN Done AS d ON
	s.[Type] = d.[Type]
	AND s.Country = d.Country
ORDER BY
	s.[Type],
	s.[Country];

(Back to Menu)

Japan-Consumption-Quartile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
;

DECLARE @StartTime DATETIME = '2022-01-15 09:00:00';
DECLARE @EndTime DATETIME = '2022-01-15 10:00:00';

WITH JapanConsumption as (
	SELECT
		Cost
	FROM
		SessionInfo WITH (nolock)
	WHERE
		Country = 'Japan'
		AND [Date] >= @StartTime
		AND [Date] <= @EndTime
)


SELECT top 1
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Cost) over() AS '25th',
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Cost) over() AS '50th',
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Cost) over()AS '75th',
	(select count(1) FROM JapanConsumption WITH (nolock)) as Volume
FROM JapanConsumption;

(Back to Menu)

ʕ •ᴥ•ʔ：整理好久的範例啊