-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathsqlserver2022-tsql-select-window.sql
121 lines (84 loc) · 3.3 KB
/
sqlserver2022-tsql-select-window.sql
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
/*
SQL Server 2022
- SELECT... WINDOW
https://www.kursysql.pl
*/
ALTER DATABASE AdventureWorks2019 SET COMPATIBILITY_LEVEL = 160
GO
USE AdventureWorks2019
GO
-- ?? jaki klient? jakie numery zamówień?
-- GROUP BY
SELECT OrderDate
,SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY OrderDate
ORDER BY OrderDate
-- PARTYCJA: data (dzień) sprzedaży
SELECT OrderDate, CustomerID, SalesOrderNumber, TotalDue
,SUM(TotalDue) OVER (PARTITION BY OrderDate) AS TotalDueDate
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
ORDER BY OrderDate ASC
SELECT CustomerID, SalesOrderNumber, OrderDate, CurrencyRateID, TotalDue
,SUM(TotalDue) OVER (PARTITION BY OrderDate) AS TotalDueDateSUM
,AVG(TotalDue) OVER (PARTITION BY OrderDate) AS TotalDueDateAVG
,MIN(TotalDue) OVER (PARTITION BY OrderDate) AS TotalDueDateMIN
,MAX(TotalDue) OVER (PARTITION BY OrderDate) AS TotalDueDateMAX
,COUNT(*) OVER (PARTITION BY OrderDate) AS TotalDueDateCOUNT
,COUNT(CurrencyRateID) OVER (PARTITION BY OrderDate) AS TotalDueDateCOUNT_CurrencyRateID
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
ORDER BY OrderDate ASC
-- SQL Server 2022
SELECT CustomerID, SalesOrderNumber, OrderDate, CurrencyRateID, TotalDue
,SUM(TotalDue) OVER win AS TotalDueDateSUM
,AVG(TotalDue) OVER win AS TotalDueDateAVG
,MIN(TotalDue) OVER win AS TotalDueDateMIN
,MAX(TotalDue) OVER win AS TotalDueDateMAX
,COUNT(*) OVER win AS TotalDueDateCOUNT
,COUNT(CurrencyRateID) OVER win AS TotalDueDateCOUNT_CurrencyRateID
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
WINDOW win AS (PARTITION BY OrderDate)
ORDER BY OrderDate ASC
-- funkcje szeregujące
SELECT CustomerID, SalesOrderNumber, OrderDate, CurrencyRateID, TotalDue
,RANK() OVER (PARTITION BY OrderDate ORDER BY TotalDue DESC) AS TotalDueRANK
,DENSE_RANK() OVER (PARTITION BY OrderDate ORDER BY TotalDue DESC) AS TotalDueDENSE_RANK
,ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY TotalDue DESC) AS TotalDueROW_NUMBER
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
ORDER BY CustomerID
SELECT CustomerID, SalesOrderNumber, OrderDate, CurrencyRateID, TotalDue
,RANK() OVER win AS TotalDueRANK
,DENSE_RANK() OVER win AS TotalDueDENSE_RANK
,ROW_NUMBER() OVER win AS TotalDueROW_NUMBER
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
WINDOW win AS (PARTITION BY OrderDate ORDER BY TotalDue DESC)
ORDER BY CustomerID
SELECT CustomerID, SalesOrderNumber, OrderDate, CurrencyRateID, TotalDue
,SUM(TotalDue) OVER win1 AS TotalDueDateSUM
,SUM(TotalDue) OVER win2 AS TotalDueDateRunningSUMDates
,SUM(TotalDue) OVER win3 AS TotalDueDateRunningSUM
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
WINDOW win1 AS (PARTITION BY OrderDate),
win2 AS (ORDER BY SalesOrderNumber),
win3 AS (win2 PARTITION BY OrderDate)
ORDER BY OrderDate ASC
-- Poziom zgodności min 160
ALTER DATABASE AdventureWorks2019 SET COMPATIBILITY_LEVEL = 150
GO
SELECT CustomerID, SalesOrderNumber, OrderDate, CurrencyRateID, TotalDue
,RANK() OVER win AS TotalDueRANK
,DENSE_RANK() OVER win AS TotalDueDENSE_RANK
,ROW_NUMBER() OVER win AS TotalDueROW_NUMBER
FROM Sales.SalesOrderHeader
WHERE OrderDate > '20110531'
WINDOW win AS (PARTITION BY OrderDate ORDER BY TotalDue DESC)
ORDER BY CustomerID
ALTER DATABASE AdventureWorks2019 SET COMPATIBILITY_LEVEL = 160
GO