下面是我試圖看到投手在前 98% 的投球局中的表現。我正在嘗試過濾 NTILE() 視窗函式，但它拋出了一個錯誤。
query = """
WITH pitching_cte AS
(
SELECT
player,
player_id,
season,
CAST(strftime('%Y', birth_date) AS TEXT) AS birth_year,
COUNT(*) AS num_seasons,
printf("%i.%i", outs/3, outs % 3) AS innings_pitched
FROM pitching
INNER JOIN player
USING(player_id)
GROUP BY player
)
SELECT innings_pitched,
birth_year,
player,
NTILE(100) OVER(PARTITION BY innings_pitched) pctl,
num_seasons
FROM pitching_cte
WHERE season = 2021 AND pctl >= 98;
"""
df = pd.read_sql(query, cnxn)
print(df.head(10))
當我執行此查詢時，我收到以下錯誤：
DatabaseError: Execution failed on sql '
WITH pitching_cte AS
(
SELECT
player,
player_id,
season,
CAST(strftime('%Y', birth_date) AS TEXT) AS birth_year,
COUNT(*) AS num_seasons,
printf("%i.%i", outs/3, outs % 3) AS innings_pitched
FROM pitching
INNER JOIN player
USING(player_id)
GROUP BY player)
SELECT innings_pitched,
birth_year,
player,
NTILE(100) OVER(PARTITION BY innings_pitched) pctl,
num_seasons
FROM pitching_cte
WHERE season = 2021 AND pctl >= 98;
': misuse of aliased window function pctl
如何過濾生成的百分位列？
謝謝
uj5u.com熱心網友回復：
一般來說，不能在 WHERE 子句的 SELECT 串列中使用定義的別名。
另一個限制是視窗函式不能在 WHERE 子句中使用，因此以下內容也會失敗：
SELECT innings_pitched,
birth_year,
player,
NTILE(100) OVER(PARTITION BY innings_pitched) pctl,
num_seasons
FROM pitching_cte
WHERE season = 2021 AND (NTILE(100) OVER(PARTITION BY innings_pitched)) >= 98;
解決方案是使用另一個級別的 cte：
WITH pitching_cte AS (
SELECT
player,
player_id,
season,
CAST(strftime('%Y', birth_date) AS TEXT) AS birth_year,
COUNT(*) AS num_seasons,
printf("%i.%i", outs/3, outs % 3) AS innings_pitched
FROM pitching
INNER JOIN player
USING(player_id)
GROUP BY player
), cte AS (
SELECT innings_pitched,
birth_year,
player,
NTILE(100) OVER(PARTITION BY innings_pitched) pctl,
num_seasons
FROM pitching_cte
WHERE season = 2021
)
SELECT *
FROM cte
WHERE pctl >= 98;
相關：為什么 where 子句中沒有視窗函式？
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/410106.html
標籤：