如何優化此 SQLite 查詢？目前運行需要 31 秒。我想在 Web 應用程式上顯示前 10 名股票上漲者和前 10 名股票下跌者。該表有 200 萬行，并且每天都有新的價格資料可用時應該會略微增加。
如果這是不可能的，我可以創建一個計劃任務來將這些結果快取到臨時資料庫表或臨時檔案中。似乎是額外的作業，但如果需要也可以。
WITH todayPrices AS (
SELECT * FROM (
SELECT *, row_number() OVER (
PARTITION BY CompanyID
ORDER BY Date DESC
) AS rn
FROM DimCompanyPrice
) a
WHERE rn = 1
ORDER BY CompanyID ASC
),
yestPrices AS (
SELECT * FROM (
SELECT *, row_number() OVER (
PARTITION BY CompanyID
ORDER BY Date DESC
) AS rn
FROM DimCompanyPrice
) a
WHERE rn = 2
ORDER BY CompanyID ASC
)
SELECT todayPrices.CompanyID, 100.0 * (todayPrices.CloseAdjusted-yestPrices.CloseAdjusted) / yestPrices.CloseAdjusted AS gain
FROM todayPrices
INNER JOIN yestPrices on todayPrices.CompanyID=yestPrices.CompanyID
ORDER BY gain DESC
LIMIT 10
我想要一些關于什么是使其表現更好的最佳方法的意見。任何輸入將不勝感激。
結果EXPLAIN QUERY PLAN：
id parent notused detail
3 0 0 MATERIALIZE 2
5 3 0 CO-ROUTINE 1
8 5 0 CO-ROUTINE 6
11 8 0 SCAN TABLE DimCompanyPrice
36 8 0 USE TEMP B-TREE FOR ORDER BY
62 5 0 SCAN SUBQUERY 6
134 3 0 SCAN SUBQUERY 1 AS a
163 3 0 USE TEMP B-TREE FOR ORDER BY
174 0 0 MATERIALIZE 4
176 174 0 CO-ROUTINE 3
179 176 0 CO-ROUTINE 7
182 179 0 SCAN TABLE DimCompanyPrice
207 179 0 USE TEMP B-TREE FOR ORDER BY
233 176 0 SCAN SUBQUERY 7
305 174 0 SCAN SUBQUERY 3 AS a
334 174 0 USE TEMP B-TREE FOR ORDER BY
345 0 0 SCAN SUBQUERY 4
357 0 0 SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (CompanyID=?)
382 0 0 USE TEMP B-TREE FOR ORDER BY
uj5u.com熱心網友回復：
我建議使用條件聚合來計算列的查詢gain。
為此，您需要進行單表掃描以根據日期對每個公司的行進行排名，然后過濾掉排名大于 2 的行，最后進行聚合：
SELECT CompanyId,
100 * (MAX(CASE WHEN rn = 1 THEN CloseAdjusted END) / MAX(CASE WHEN rn = 2 THEN CloseAdjusted END) - 1) gain
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY Date DESC) rn
FROM DimCompanyPrice
)
WHERE rn <= 2
GROUP BY CompanyId
ORDER BY gain DESC;
但是如果今天和昨天你實際上是指當前日期和前一個日期，你也可以用這個查詢來做：
SELECT CompanyId,
100 * (MAX(CASE WHEN Date = CURRENT_DATE THEN CloseAdjusted END) / MAX(CASE WHEN Date = Date(CURRENT_DATE, '-1 day') THEN CloseAdjusted END) - 1) gain
FROM DimCompanyPrice
WHERE Date >= Date(CURRENT_DATE, '-1 day')
GROUP BY CompanyId;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/370836.html
上一篇：sqlite3.OperationalError：接近“？”：語法錯誤
下一篇：在同一個表中查找重復的列