确定特定时间范围内价值的持续增长 [英] Determine persistent growth in value during a specific time range

问题描述

鉴于以下数据集示例，我应该如何构建我的 SQL 查询以确定 value 是否逐渐每天增长-通过返回true/false 结果

Given the following data set example, how should I structure my SQL query to determine if the value has gradually grown day-by-day given a time range in the query for a specific user_id by returning a true/false result

<头>

user_id	日期	value
1	4/1/2021 12:00	350
1	4/2/2021 12:00	400
1	4/3/2021 12:00	450
5	4/4/2021 12:00	560
5	4/5/2021 12:00	566
5	4/6/2021 12:00	483
5	4/7/2021 12:00	570

根据上面的例子，我们可以观察到 user_id=1 的 value 日复一日地逐渐增长，而 value 的 valuecode>user_id=5 连续 2 天增长，但在第三天反弹.

Given the above example, we can observe that the value for user_id=1 has gradually grown day after day while the value for user_id=5 has grown for 2 consecutive days, but bounced down on the third day.

因此，user_id=1 和 user_id=5 的预期结果集将是:

Therefore, the expected result-set for user_id=1 and user_id=5 would be:

<头>

user_id	value_is_increasing
1	true

user_id=5 的预期结果集将是:

<头>

user_id	value_is_increasing
5	false

声明更新:我想确定该值是否在特定时间范围内每天(连续)不断增长.此外，单个值点应被视为噪声(即使它们随着时间的推移呈上升或下降趋势，它们也会上下反弹)并影响结果.

Declaration Update: I would like to determine if the value has constantly grown within a specific time range in a day-by-day manner (consecutive). Moreover, individual value points should be considered noisy (they bounce up and down even though they trend upward or downward over time) and affect the result.

推荐答案

使用聚合:

SELECT user_id, 
       GROUP_CONCAT(value ORDER BY date) = GROUP_CONCAT(value ORDER BY value) value_is_increasing
FROM tablename
GROUP BY user_id
HAVING COUNT(*) > 1 
   AND COUNT(*) = COUNT(DISTINCT value)

条件COUNT(*) >1 将仅返回表中值多于 1 的用户.
条件 COUNT(*) = COUNT(DISTINCT value) 将仅返回表中只有不同值的用户.
GROUP_CONCAT(value ORDER BY date) 将返回值 order by date 和
GROUP_CONCAT(value ORDER BY value) 将返回值 order按值.
如果 2 个 GROUP_CONCAT() 的结果相同，这意味着 value 是单调递增的.

The condition COUNT(*) > 1 will return only users that have more than 1 values in the table.
The condition COUNT(*) = COUNT(DISTINCT value) will return only users that have only distinct values in the table.
GROUP_CONCAT(value ORDER BY date) will return values order by date and
GROUP_CONCAT(value ORDER BY value) will return values order by value.
If the results of the 2 GROUP_CONCAT() are the same this means that values are monotonically increasing.

请参阅演示.

这篇关于确定特定时间范围内价值的持续增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！