用SQL写出连续5天未登录用户,在未登录之后的天数中有连续登录过5天
时间: 2023-03-12 07:30:31 浏览: 52
SELECT user_id FROM users WHERE EXISTS (SELECT user_id FROM login_info WHERE (login_date - (SELECT MAX(login_date) FROM login_info WHERE login_date < DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY)) <= 5 DAYS)
相关问题
连续交易五天的客户id 交易日期 交易金额 sql怎么实现
可以使用 Hive SQL 来实现查询连续交易五天的客户 ID、交易日期和交易金额。假设有一个名为 `transaction` 的交易表,包含了客户 ID、交易日期和交易金额等字段,可以按照以下步骤进行查询:
1. 对交易表按照客户 ID 和交易日期进行分组,计算每个客户每天的交易金额总和。
```
SELECT customer_id, transaction_date, SUM(amount) AS total_amount
FROM transaction
GROUP BY customer_id, transaction_date
```
2. 对第一步的结果按照客户 ID 进行分组,使用 Hive 内置函数 `LAG` 和 `LEAD` 计算每个客户每天与前后两天的交易差值。如果这三天的交易金额总和都不为零,则表示客户在这三天内进行了连续交易。
```
WITH daily_transaction AS (
SELECT customer_id, transaction_date, SUM(amount) AS total_amount
FROM transaction
GROUP BY customer_id, transaction_date
)
SELECT t1.customer_id, t1.transaction_date, t1.total_amount
FROM (
SELECT customer_id, transaction_date, total_amount,
LAG(total_amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS prev_amount,
LEAD(total_amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS next_amount
FROM daily_transaction
) t1
WHERE t1.total_amount + t1.prev_amount + t1.next_amount > 0
```
3. 对第二步的结果进行过滤,只保留连续交易五天的客户 ID、交易日期和交易金额。
```
WITH daily_transaction AS (
SELECT customer_id, transaction_date, SUM(amount) AS total_amount
FROM transaction
GROUP BY customer_id, transaction_date
), transaction_diff AS (
SELECT t1.customer_id, t1.transaction_date, t1.total_amount,
LAG(total_amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS prev_amount,
LEAD(total_amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS next_amount
FROM daily_transaction t1
)
SELECT t1.customer_id, t1.transaction_date, t1.total_amount
FROM (
SELECT customer_id, transaction_date, total_amount,
SUM(CASE WHEN total_amount + prev_amount + next_amount > 0 THEN 1 ELSE 0 END)
OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS trans_count
FROM transaction_diff
) t1
WHERE t1.trans_count = 5
```
上述 SQL 语句中,第一步使用 `GROUP BY` 对交易表进行分组,计算每个客户每天的交易金额总和;第二步使用 `LAG` 和 `LEAD` 函数计算每个客户每天与前后两天的交易差值,然后过滤出连续交易三天的客户;第三步使用 `SUM` 和 `OVER` 函数计算每个客户连续交易的天数,然后过滤出连续交易五天的客户。
在oracle中使用分析函数求出该周期内最长的工作周(注意可能有多个) ,输出包括:工作周第一天,该工作周包含的工作天数。要求 1 条 SQL 语句完成。
可以使用以下 SQL 语句来实现:
```
SELECT start_date, max(work_days) AS max_work_days
FROM (
SELECT start_date, COUNT(*) AS work_days
FROM (
SELECT date_column, date_column - TRUNC(date_column, 'IW') + 1 AS day_of_week,
date_column - (date_column - TRUNC(date_column, 'IW')) AS start_date
FROM your_table
)
WHERE day_of_week BETWEEN 1 AND 5
GROUP BY start_date
)
GROUP BY start_date
ORDER BY max_work_days DESC;
```
解释一下:
首先,我们使用了 Oracle 的 `TRUNC` 函数来获取每个日期所在的周的第一天,然后计算出该日期是该周的第几天。然后我们过滤出工作日(即 `day_of_week` 在 1 到 5 之间的日期),并计算出每个工作周的第一天和工作天数。最后,我们使用分析函数 `MAX` 来找出最长的工作周,并将结果按工作天数降序排列。
需要注意的是,这里假设一个工作周从周一到周五,如果你的工作日不是这样的,需要根据实际情况修改查询条件。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![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)