sql - T-SQL : SELECT posts where trend is downwards on two time periods

Question

我想避免在表格上进行子选择来比较两个时期以及趋势是否向下。

此选择获取一个周期内的百分比

SELECT user, (SUM(value1)/SUM(value2)) AS percentage1
FROM table
WHERE (date BETWEEN @start1 AND @end1)
GROUP BY user
ORDER BY 2

这个查询得到了我想要的结果，但效率不是很高，因为有超过 1 亿行。

SELECT t1.user, (SUM(t1.value1)/SUM(t1.value2)) AS percentage1,
(SELECT (SUM(t2.value1)/SUM(t2.value2)) AS percentage2
FROM table AS t2
WHERE t2.userID = t1.userID
AND (t2.date BETWEEN @start2 AND @end2)
)
FROM table AS t1
WHERE (t1.date BETWEEN @start1 AND @end1)
AND (SUM(t1.value1)/SUM(t1.value2)) < (SELECT (SUM(t2.value1)/SUM(t2.value2))
FROM table AS t2
WHERE t2.userID = t1.userID
AND (t2.date BETWEEN @start2 AND @end2)
)
GROUP BY t1.user

有更好的方法吗？一种解决方案可能是只设置一个日期期间，然后按月(日期)、年(日期)进行分组，并与上个月进行比较，而不是使用两个确切的日期。但是按月份和年份分组只会为每个用户提供几行，而我想避免这种情况。

只是想要一个像这样干净的结果:

Adam, 43%, 47%
Lisa, 22%, 25%
John, 18%, 34%

排除这样的行，因为趋势百分比较低

Bill, 24%, 18%
Nina, 84%, 56%

SQL-Server 2016 Enterprise 是数据库。

Answer 1

你可以试试这个。

SELECT G1.user, G1.percentage1, G2.percentage2 
FROM 
    ( SELECT t1.user, t1.userID
        (SUM(t1.value1)/SUM(t1.value2)) AS percentage1,
      FROM table AS t1
      WHERE 
        (t1.date BETWEEN @start1 AND @end1)
      GROUP BY t1.user, t1.userID
    ) AS G1
    INNER JOIN (
        SELECT t2.userID, (SUM(t2.value1)/SUM(t2.value2)) AS percentage2
        FROM table AS t2
        WHERE 
            (t2.date BETWEEN @start2 AND @end2)
        GROUP BY t2.userID
    ) AS G2 ON G1.userID = G2.userID
WHERE 
    G1.percentage1 < G2.percentage2

但是如果您只想使用表格中的一项选择，那么您也可以尝试此操作。

DECLARE @start DATE
DECLARE @end DATE

SET @start = CASE WHEN @start1 < @start2 THEN @start1 ELSE @start2 END
SET @end = CASE WHEN @end1 > @end2 THEN @end1 ELSE @end2 END

SELECT * FROM (
    SELECT t.userID, 
        SUM( CASE WHEN t.date BETWEEN @start1 AND @end1 THEN t.value1 END ) / SUM( CASE WHEN t.date BETWEEN @start1 AND @end1 THEN t.value2 END ) AS percentage1, 
        SUM( CASE WHEN t.date BETWEEN @start2 AND @end2 THEN t.value1 END ) / SUM( CASE WHEN t.date BETWEEN @start2 AND @end2 THEN t.value2 END ) AS percentage2, 
    FROM table AS t
    WHERE 
        (t.date BETWEEN @start AND @end)
    GROUP BY t.userID
) AS SQ WHERE percentage1 < percentage2