每组优化的最大n

Question

如何优化此SQLite查询？目前运行需要31秒。我想在一个网络应用程序上显示前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

Answer 1

我建议使用条件聚合来计算列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;