-
Notifications
You must be signed in to change notification settings - Fork 0
/
NotSupportedInSPARQL1.1.sql
257 lines (220 loc) · 6.79 KB
/
NotSupportedInSPARQL1.1.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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
-- SQL features NOT Supported in SPARQL 1.1
--Query: Select the 3 most recent orders from each customer.
SELECT
cst.CustomerID,
cst.City,
cpp.OrderID,
cpp.OrderDate
FROM
Customer AS cst
-- For each customer record, go and get the two most recent orders.
-- An INNER JOIN could've been used, however, CROSS APPLY is more efficient when combined with SELECT TOP.
CROSS APPLY
(
SELECT TOP 3
ord.OrderID, ord.OrderDate, ord.CustomerID
FROM
[Order] AS ord
WHERE
ord.CustomerID = cst.CustomerID -- reference to the outer query (correlated subquery)
ORDER BY
ord.OrderDate DESC
) AS cpp
ORDER BY
cst.CustomerID,
cst.City,
cpp.OrderDate DESC
-- Window Functions
-- Compute aggregated values such as moving averages, cumulative aggregates, running totals,
-- or a top N numbering and ranking per group results.
-- Reference:
-- https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017
-- Query: Select the 3 most recent orders from each customer
-- This query replaces the previous one by using the more efficient Window Function.
SELECT
ptt.*
FROM
(
SELECT
cst.CustomerID,
cst.City,
ord.OrderID,
ord.OrderDate,
ROW_NUMBER() OVER(PARTITION BY cst.CustomerID ORDER BY ord.OrderDate DESC) AS [RowNumber]
FROM Customer AS cst
INNER JOIN [Order] AS ord
ON cst.CustomerID = ord.CustomerID
) ptt
WHERE
ptt.[RowNumber] <= 3
-- Query: Top 5 most expensive product in each product category
-- ROW_NUMBER is used to number the rows sequentially in the partition.
SELECT
ptt.*
FROM
(
SELECT
ctg.CategoryName,
prd.ProductName,
prd.UnitPrice,
ROW_NUMBER() OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [RowNumber]
FROM
Product prd
INNER JOIN Category ctg
ON prd.CategoryID = ctg.CategoryID
) ptt
WHERE
ptt.[RowNumber] <= 5
ORDER BY
ptt.CategoryName,
ptt.RowNumber
-- Query: Order total quantity and percentage by product
SELECT
ord.OrderID,
ord.ProductID,
ord.Quantity,
SUM(ord.Quantity) OVER(PARTITION BY ord.OrderID) AS Total,
CAST(1. * ord.Quantity / SUM(ord.Quantity) OVER(PARTITION BY ord.OrderID) * 100 AS DECIMAL(5,2)) AS "PercByProduct"
FROM
OrderDetail ord
WHERE
ord.OrderID IN(10248,10249, 10250);
GO
-- Query: Top 3 most expensive product in each product category (RANK, DENSE_RANK, NTILE)
-- RANK: same as ROW_NUMBER, however it provides the same numeric value for ties.
-- DENSE_RANK: the same as RANK, however it has no gaps in the ranking values.
-- NTILE: distributes the rows in an ordered partition into a specified number of groups.
-- Reference:
-- https://docs.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql?view=sql-server-ver15
SELECT
ptt.*
FROM
(
SELECT
ctg.CategoryName,
prd.ProductName,
prd.UnitPrice,
ROW_NUMBER() OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [RowNumber],
RANK() OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [RANK],
DENSE_RANK() OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [DENSE_RANK],
NTILE(6) OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [NTILE]
FROM
Product prd
INNER JOIN Category ctg
ON prd.CategoryID = ctg.CategoryID
) ptt
WHERE
ptt.[RowNumber] <= 3
ORDER BY
ptt.CategoryName,
ptt.RowNumber
-- Query: Apply a 10% discount on the top 3 most expensive product in each product category
-- Could have used a temp table to save the list of products affected in order to be able to
-- check if the discount had been applied successfully.
UPDATE
Product
SET
UnitPrice = UnitPrice * 0.9
WHERE
ProductID IN
(
SELECT
ptt.ProductID
FROM
(
SELECT
prd.ProductID,
ROW_NUMBER() OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [RowNumber]
FROM
Product prd
INNER JOIN Category ctg
ON prd.CategoryID = ctg.CategoryID
) ptt
WHERE
ptt.[RowNumber] <= 3
)
-- Query: Apply a 10% discount on the top 3 most expensive product in each product category.
-- This time using a temp table to save the list of products affected.
-- This is a simple example, but a temp table could be used to store a dataset that goes under
-- many calculations before being committed to the actual table on the database.
SELECT
ptt.ProductID,
ptt.UnitPrice
INTO
#ProdDiscount
FROM
(
SELECT
prd.ProductID,
prd.UnitPrice,
ROW_NUMBER() OVER(PARTITION BY ctg.CategoryID ORDER BY prd.UnitPrice DESC) AS [RowNumber]
FROM
Product prd
INNER JOIN Category ctg
ON prd.CategoryID = ctg.CategoryID
) ptt
WHERE
ptt.[RowNumber] <= 3
UPDATE
Product
SET
UnitPrice = UnitPrice * 0.9
WHERE
ProductID IN (SELECT ProductID FROM #ProdDiscount)
-- Displaying price after discount for the products affected (which is not possible in the previous example,
-- unless Temporal Tables, Change Tracking, etc are used).
SELECT
prd.ProductID,
prd.UnitPrice
FROM
Product prd
INNER JOIN #ProdDiscount tpr
ON prd.ProductID = tpr.ProductID
ORDER BY
prd.ProductID
DROP TABLE IF EXISTS #ProdDiscount
-- AdventureWorks Queries ----------------------------
USE AdventureWorks2017;
SELECT
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,
FirstName,
LastName,
ROUND(SalesYTD,2,1) AS "Sales YTD"
FROM
Sales.vSalesPerson
WHERE
TerritoryName IS NOT NULL
AND SalesYTD <> 0;
USE AdventureWorks2017;
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO
USE AdventureWorks2017;
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
,DENSE_RANK() OVER
(PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO
USE AdventureWorks2017;
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO