用SQL计算:每个用户余额>=1000的最长连续天数,譬如uid1余额大于等于1000最长连续天数是2,uid2是3; 用户余额表:useramt(每个用户,每天都有一条记录,不管余额是否为0) 用户标识 dt 金额 用户界面1 20200501 1000 uid_1 20200502 0 uid_1 20200503 1000 uid_1 20200504 1000 uid_1 20200505 0 uid_2 20200501 1000 uid_2 20200502 2000 uid_2 20200503 1000 uid_2 20200504 900 uid 2 20200505 1000
时间: 2024-03-06 12:50:23 浏览: 27
可以使用 MySQL 的变量和子查询来实现。
首先,需要按照用户分组,按照日期升序排序,并且使用变量 `@consecutive_days` 记录当前连续天数。然后,对于每个用户,使用子查询计算最长连续天数。
具体实现如下:
```sql
SELECT
user_id,
MAX(consecutive_days) AS max_consecutive_days
FROM (
SELECT
user_id,
dt,
@consecutive_days := IF(amt >= 1000, @consecutive_days + 1, 0) AS consecutive_days
FROM
useramt,
(SELECT @consecutive_days := 0) AS t
ORDER BY
user_id, dt
) AS t
WHERE
amt >= 1000
GROUP BY
user_id;
```
解释一下:
外层查询使用 `GROUP BY` 将结果按照用户分组,然后对于每个用户,计算最长连续天数。
内层查询使用变量 `@consecutive_days` 记录当前连续天数,如果余额大于等于 1000,则当前连续天数加 1,否则重置为 0。
最后,根据余额大于等于 1000 条件过滤结果,并按照用户分组,并按照连续天数取最大值。
相关问题
用SQL计算:每个用户 余额 >= 1000的 最长连续天数,譬如uid_1 余额大于等于1000最长连续天数是2,uid_2 是3; 用户余额表:user_amt(每个用户,每天都有一条记录,不管余额是否为0)
可以使用以下SQL语句来计算每个用户余额大于等于1000的最长连续天数:
```
WITH
cte AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY uid ORDER BY date) AS rn
FROM user_amt
WHERE amt >= 1000
)
SELECT
uid,
MAX(rn - ROW_NUMBER() OVER (PARTITION BY uid, grp ORDER BY date)) AS max_consecutive_days
FROM cte
GROUP BY uid, grp
```
该语句使用了常表达式(CTE)和窗口函数来实现。首先,使用CTE找到每个用户余额大于等于1000的记录,并为它们分配一个序号。接着,使用窗口函数计算每个记录所在的连续区域(使用rn减去组内的行号)。最后,按照用户和连续区域进行分组,并找到每个分组的最大连续天数。
每天用户表都有一条余额记录,求每个用户余额大于1000的连续天数?
可以通过 SQL 语句来查询每个用户余额大于1000的连续天数,假设用户表为 `user_table`,余额记录为 `balance`,日期为 `date`,用户 ID 为 `user_id`,可以使用以下语句:
```sql
SELECT user_id, MAX(consecutive_days) AS max_consecutive_days
FROM (
SELECT user_id, date, balance,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rn,
ROW_NUMBER() OVER (PARTITION BY user_id, CASE WHEN balance > 1000 THEN 0 ELSE 1 END ORDER BY date) AS grp
FROM user_table
) t
WHERE balance > 1000
GROUP BY user_id, grp - rn
```
这个语句的核心是使用了 Window 函数 `ROW_NUMBER()` 和 `PARTITION BY` 子句来给每条记录分配一个序号,然后在计算连续天数时,使用了一个小技巧,即将每个余额大于1000的记录和其前面所有记录分到同一组,将余额小于等于1000的记录和其前面所有记录分到同一组,这样就可以通过组内的 `ROW_NUMBER()` 来计算连续天数。最后,使用 `GROUP BY` 和 `MAX()` 函数,可以找到每个用户连续天数的最大值。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)