You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
with temp as (
select
account_id,
date_format(day, '%Y%m') yearmonth
from transactions join accounts using(account_id)
where type ='creditor'group by account_id, yearmonth
havingsum(amount) >max(max_income)
)
select
distinct account_id
from temp where (account_id, period_add(yearmonth, 1)) in (
select account_id, yearmonth from temp
);
解析
使用子查询的方式查询出结果
使用 with 创建临时表 temp
输出字段为 account_id 和 yearmonth
使用 group by 分支，分组的条件是 account_id 和 yearmonth
使用 having 筛选出每个用户每个月的总收入是否大于这个用户这个的最大收入
使用 sum 是因为每个用户每个月可能有多笔收入，需要将它们每一笔都相加
max 可以替换为 min 或者 any_value 因为每个用户每个月的最大收入是一样的，取出其中一个就可以了
with temp as (
select
account_id,
date_format(day, '%Y%m') yearmonth
from transactions join accounts using(account_id)
where type ='creditor'group by account_id, yearmonth
havingsum(amount) >max(max_income)
)
select
distinct account_id
from temp t1 join temp t2 using(account_id)
wheret1.yearmonth=t2.yearmonth+1;
-- where 可改成
period_diff(t1.yearmonth, t2.yearmonth) =1
select
distinct account_id
from (
select
account_id,
month,
month - cast(row_number() over(partition by account_id) as signed) diff
from (
select
account_id,
month(day) month
from accounts join transactions using(account_id)
where type ='creditor'group by account_id, month
havingsum(amount) >max(max_income)
order by account_id, month
) temp1
) temp2
group by account_id, diff
havingcount(diff) >=2;
select
distinct account_id
from (
select
account_id,
max_income,
month,
income,
lead(month, 1, 0)
over (partition by account_id order by account_id, month ②) as next_month
from (
select
account_id,
max_income,
date_format( day, '%y%m' ) as'month',
sum(amount)
over (partition by account_id, date_format(day, '%y%m')) as income
from accounts join transactions using(account_id)
where type ='creditor'-- order by month ①
) as temp1 where income > max_income
) as temp2
where next_month !=0and period_diff(next_month, month) =1
解析
使用 lead 窗口函数实现的，通过窗口函数对相同的 account_id 进行排序后，将下一行的 month 作为当前行的 next_month
使用 lag 也是可以实现的，下面步骤解释是使用 lead 做的
lag(month, 1, 0)
over (partition by account_id order by account_id, month desc) as next_month
但是这个方法折磨了我好久，主要是排序的问题 ② 处，用 lead() 不在 over 中使用 order by ，输出的顺序可能不是你想要的。
lead(month, 1, 0)
over (partition by account_id order by account_id, month) as next_month
-- 示例，不代表真实的查询结果
month | next_month
2105 | 20162106 | 21072107 | 0
筛选出当月总收入 income 大于当月最大收入 max_income
为什么 temp1 表中已经查询出 income 和 max_income ，不能在 temp1 表中晒寻呢，而是要跑到 temp2 中去做呢？因为这涉及到 SQL 执行的顺序，在执行 where 时，窗口函数还没有运行，所以是拿不到 income 的，也就是说不能在 temp1 做这个筛选
题目
题目链接：可疑银行账户
type是枚举类型（包含'Creditor'，'Debtor'），其中'Creditor'表示用户向其账户存入资金，'Debtor'表示用户从其账户取出资金。写一个 SQL 查询语句列示所有的可疑账户。
如果一个账户在连续两个及以上月份中总收入超过最大收入（
max_income），那么这个账户可疑。 账户当月总收入是当月存入资金总数（即transactions表中type字段的'Creditor'）。返回的结果表以
transaction_id排序。分析
这题考的是如何判断月份是否连续，只需要两个月连续，这个用户就需要查询出来
判断下一个月的方法核心是
period_add，所以需要有两个字段month和next_month，month字段可以通过内部函数进行转换就可以拿到了，复杂的地方就在于next_month怎么拿到：方法三和其他三个方法不一样，它没有获取
next_month，而是对每个用户的所有月份进行了排序，从而得到连续的月份。SQL：方法一
解析
使用子查询的方式查询出结果
with创建临时表tempaccount_id和yearmonthgroup by分支，分组的条件是account_id和yearmonthhaving筛选出每个用户每个月的总收入是否大于这个用户这个的最大收入sum是因为每个用户每个月可能有多笔收入，需要将它们每一笔都相加max可以替换为min或者any_value因为每个用户每个月的最大收入是一样的，取出其中一个就可以了account_id和yearmonthwhere如果要筛选多个条件，需要用括号括起来并且使用in操作符period_add(yearmonth, 1)的结果是下一个月distinct去重，因为一个用户可能有多个连续月收入可疑，但是他们又不连续，查询出的结果，一个用户就会出现多次。SQL：方法二
解析
使用两表连接的方式查询出结果
temp和方法一是一样的t1.yearmonth = t2.yearmonth + 1t2.yearmonth + 1是t1.yearmonth下一个月+1判断是否是下个月就判断不出来了period_diff(t1.yearmonth, t2.yearmonth) = 1distinct去重，因为一个用户可能有多个连续月收入可疑，但是他们又不连续，查询出的结果，一个用户就会出现多次。SQL：方法三
解析
判断连续的数方法：将数进行升序或者降序排列，然后用这个数减去排列的序号，如果是连续的数，那么它们的结果是相同的。
从上面可以得出
1 - 4、6 - 8、10 - 11是连续的数temp1需要提供month、account_id这两个字段accounts和transactions两表连接，筛选出type = 'creditor'的数据month()计算出月份account_id和month进行分组temp2需要提供account_id、month、diff这三个字段diff字段使用row_number()按照account_id分组，就能得到这个用户月份收入的排名cast(... as signed)转换下类型（为啥要转换类型，目前不清楚）temp2distinct的作用是，一名用户可能存在多个连续2个月以上的可疑收入，但是这里只需要一个account_id，所以需要去重account_id和diff进行分组，因为通过第2步可以得到连续的月份，diff是相等的diff大于等于2的数据，就是可疑用户SQL：方法四
解析
使用
lead窗口函数实现的，通过窗口函数对相同的account_id进行排序后，将下一行的month作为当前行的next_monthlag也是可以实现的，下面步骤解释是使用lead做的但是这个方法折磨了我好久，主要是排序的问题 ② 处，用
lead()不在over中使用order by，输出的顺序可能不是你想要的。temp1表：查询出每个用户每个月的总收入income，以及将日期进行格式化。over的，省去了后面用group by分组的步骤（简化查询）temp2表：使用窗口函数lead()按照用户分组，月份升序排列，会得到next_month使用
lead(month, 1, 0)，因为是按照升序排列，所以next_month就是当前行下面一行筛选出当月总收入
income大于当月最大收入max_incometemp1表中已经查询出income和max_income，不能在temp1表中晒寻呢，而是要跑到temp2中去做呢？因为这涉及到 SQL 执行的顺序，在执行where时，窗口函数还没有运行，所以是拿不到income的，也就是说不能在temp1做这个筛选用
period_diff(next_month, month) = 1筛选出连续两月都超过的总收入的记录next_month != 0是因为period_diff不能接受0作为参数，所以要把next_month = 0的去掉，同时也说明next_month = 0的记录是正常记录，不是可疑记录distinct account_id，因为题目只要求查找出account_id，但是有可能一个用户可能有多个连续月存在可以，所以就需要使用distinct去重心路历程：
毫无头绪
account_id = 12的用户是可疑账户，每个月的amount都大于它的max_income这里用
lead(month, 1, 0) over(partition by account_id)筛选不出来，这里是倒序排列的我想到可以用
order by将month排序一下，是不是就可以了？通过在 ① 处加了
order by month，account_id = 12这条数据筛选出来了，我以为没问题了，提交代码运行，又有一条account_id = 2是一个可疑账户，我的代码没有查询出来。这什么情况啊，一度怀疑不能用
lead窗口函数。这是
account_id = 2的数据，5、6、7月，连续三个月超过最大收入：加上 ① 处
order by，查询出来的顺序乱掉了 ，month是无序的：lead后这个顺序是怎么来的，我以为还会保持temp1表中的顺序然后我就准备放弃了，使用窗口函数问题太多了吧，正准备放弃的时候，我想到了在
over中使用order by排序，顺序会怎样峰回路转
我期望
month顺序是正序排列。然后将下一行作为next_month，后面才能通过比较next_month和month确定两个月是否连续。然后就在 ② 处添加了排序，
account_id = 2查询出来了，问题解决了。提交运行，通过~~~
既然顺序不是我预期的，那就需要使用
order by进行排序 ② 处总结
lead窗口函数了解不深，不太会使用，忘记了在over中是可以使用order by的测试数据
SQL1
SQL2
SQL3
The text was updated successfully, but these errors were encountered: