我正在嘗試在我的資料庫中獲取分層資料。這是我的初始代碼。
SELECT TOP 5
year, location_state, location_city,
COUNT(tf.customer_key) Number_of_Customers
FROM TransactionFact tf
JOIN LocationDim as ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim dd
ON dd.date_key = tf.order_date_key
WHERE dd.year = 2016 and location_state = 'SP'
GROUP BY dd.year, ld.location_state, ld.location_city
ORDER BY dd.year DESC, Number_of_Customers DESC
這是結果，result。
基本上，在查詢中，我想要做的是不要在子句中對location_state進行硬編碼WHERE。我希望把它的動態，使我所得到的是前5 個城市中的每個狀態。
以下是LocationDim表的列名
location_key
location_zip_code_prefix
location_state
location_city
編輯：我需要的是這樣的東西。
------ ---------------- --------------- ---------------------
| year | location_state | location_city | Number_of_Customers |
------ ---------------- --------------- ---------------------
| 2016 | STATE_1 | city_1 | 100 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_1 | city_2 | 90 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_1 | city_3 | 89 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_1 | city_4 | 88 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_1 | city_5 | 20 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_2 | city_1 | 100 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_2 | city_2 | 45 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_2 | city_3 | 23 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_2 | city_4 | 10 |
------ ---------------- --------------- ---------------------
| 2016 | STATE_2 | city_5 | 5 |
------ ---------------- --------------- ---------------------
PS：抱歉，這是我在stackoverflow 中的第一個問題。如果此問題重復，請洗掉鏈接，我會試一試。提前謝謝你。
uj5u.com熱心網友回復：
如果你計算一個 row_number 那么你可以過濾它。
SELECT *
FROM
(
SELECT
dd.[year]
, ld.location_state
, ld.location_city
, COUNT(tf.customer_key) AS total_customers
, rn = ROW_NUMBER() OVER (PARTITION BY ld.location_state, dd.[year]
ORDER BY COUNT(tf.customer_key) DESC)
FROM TransactionFact AS tf
JOIN LocationDim AS ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim AS dd
ON dd.date_key = tf.order_date_key
WHERE dd.[year] = 2016
GROUP BY ld.location_state, ld.location_city, dd.[year]
) q
WHERE rn <= 5
ORDER BY location_state, [year], rn
uj5u.com熱心網友回復：
您可以使用添加行號 ROW_NUMBER() OVER (PARTITION BY ORDER BY )
下面的查詢磁區記錄location_state并按number_of_customer順序添加行號：
Select * from
inn.*, ROW_NUMBER() OVER (PARTITION BY location_state ORDER BY Number_of_Customers DESC) AS rn
(
SELECT year,
location_state,
location_city,
COUNT(tf.customer_key) Number_of_Customers
FROM TransactionFact tf
JOIN LocationDim as ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim dd
ON dd.date_key = tf.order_date_key
WHERE dd.year = 2016
GROUP BY dd.year, ld.location_state, ld.location_city
) inn
在此之后，您可以輕松過濾并選擇 top5 或任何其他順序...
注意：我使用您的查詢作為內部查詢。我沒有機會測驗它，因為沒有小提琴
uj5u.com熱心網友回復：
您可以嘗試使用cross apply運算子將每個 location_state 的值傳遞給您的查詢，如下所示：
;With STATES As (
SELECT location_state
FROM LocationDim
GROUP BY location_state)
SELECT T.[year], T.location_state, T.location_city, T.Number_of_Customers
FROM STATES CROSS APPLY (
SELECT TOP 5
[year], location_state, location_city,
COUNT(tf.customer_key) AS Number_of_Customers
FROM TransactionFact tf
JOIN LocationDim as ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim dd
ON dd.date_key = tf.order_date_key
WHERE dd.[year] = 2016 and location_state = STATES.location_state
GROUP BY dd.[year], ld.location_state, ld.location_city
ORDER BY dd.[year] DESC, Number_of_Customers DESC) As T
uj5u.com熱心網友回復：
所以，基本上這里發生了什么：
SELECT TOP 5
dd.[year], ld.location_state, ld.location_city, COUNT(tf.customer_key) Number_of_Customers
FROM TransactionFact tf
JOIN LocationDim as ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim dd
ON dd.date_key = tf.order_date_key
WHERE dd.year = 2016 and location_state = 'SP'
GROUP BY dd.year, ld.location_state, ld.location_city
ORDER BY dd.year DESC, Number_of_Customers DESC;
是您只從該查詢中選擇前 5 個結果。
但是你想要的是得到所有的結果，按年份和州排名，并且只從每個州中取前 5 名？
我會使用專為您正在查看的場景設計的RANK()功能。我會將其顯示為您查詢中的附加列：
SELECT * FROM ( SELECT dd.[year], ld.location_state, ld.location_city, COUNT(tf.customer_key) Number_of_Customers,
RANK() OVER(PARTITION BY dd.[year], ld.location_state, ld.location_city
ORDER BY Number_of_Customers DESC) r
FROM TransactionFact tf
JOIN LocationDim as ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim dd
ON dd.date_key = tf.order_date_key
WHERE dd.year = 2016 and location_state = 'SP'
GROUP BY dd.year, ld.location_state, ld.location_city
) x WHERE x.r <= 5
ORDER BY x.[year] desc, x.location_state, x.r
或者，您可以使用CTE（公用表運算式）來保存第一個查詢的結果，然后再應用RANK：
;WITH cte AS(
SELECT dd.[year], ld.location_state, ld.location_city, COUNT(tf.customer_key) Number_of_Customers,
RANK() OVER(PARTITION BY [year], location_state, location_city
ORDER BY Number_Of_Customers DESC) r
FROM TransactionFact tf
JOIN LocationDim as ld
ON ld.location_key = tf.seller_location_key
JOIN DateDim dd
ON dd.date_key = tf.order_date_key
WHERE dd.year = 2016 and location_state = 'SP'
GROUP BY dd.year, ld.location_state, ld.location_city
)
SELECT *
FROM cte
WHERE r <= 5;
作為免責宣告，我只是在 the 之前放置一個分號，WITH以表明如果事先有一個不以分號結尾的陳述句，那么該陳述句將引發錯誤。
編輯：添加，使用RANK意味著結果按值排序。因此，如果兩個城市有 30,000 名客戶，那么他們都將獲得相同的 RANK 值（類似于當人們在一輪高爾夫球中打平時，他們在排行榜中所做的事情）。這意味著您將從每個狀態獲得至少 5 個結果 - 如果您想要 5 個，而不管系結值如何，那么您可以ROW_NUMBER以相同的方式使用。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/379314.html
標籤：sql-server 查询语句