greatest n per group optimization-CodePudding

How can I optimize this SQLite query? It currently takes 31 seconds to run. I want to display the top 10 stock gainers and top 10 stock losers on a web application. The table has 2m rows and should increase ever so slightly each day new price data is available.

If this is not possible, I could create a scheduled task to cache these results to a temp db table or a temp file. Seems like extra work but possible if needed.

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

I would like some input in regards to what would be the best approach to getting this to perform better. Any input would be appreciated.

Results of 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

CodePudding user response：

I would suggest a query that uses conditional aggregation to calculate the column gain.
For this you will need a single table scan to rank the rows of each company based on date, then filter out the rows with ranking greater than 2 and finally aggregation:

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;

But if by today and yesterday you actually mean the current date and the previous date, you could also do it with this query:

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;