江苏龙网

  1. 首页 > 人文 > >

搞定SQL！5个棘手SQL查询的解决方法( 二 )


搞定SQL！5个棘手SQL查询的解决方法
本文插图
查询4
该事务表由transaction_id, user_id, transaction_date,product_id, and quantity（交易ID ， 用户ID ， 交易日期 ， 产品ID和数量）组成 。 需要查询多天来购买产品的用户数量（注意 ， 给定用户可以在一天内购买多个产品） 。
搞定SQL！5个棘手SQL查询的解决方法
本文插图
· 本文解决方案
为了解决该查询 ， 不能直接计算user_id的出现次数 ， 由于给定用户在一天中可以多次购买 ， user_id或许会有多次返回 。 因此 ， 只有当存在多个不同日期与给定的user_id相关联时 ， 才意味着该用户多天购买了产品 。 按照相同方法 ， 进行查询编写 。 （内部查询）
SELECT COUNT(user_id) FROM ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(DISTINCT DATE(date))> 1 ) t1
由于问题询问的是user_id的数量 ， 而不是user_id本身 ， 因此在外部查询中使用 COUNT。
搞定SQL！5个棘手SQL查询的解决方法
本文插图
查询5
给定一个订阅表 ， 其中包含每个用户订阅的开始和结束日期 。 需要编写一个查询 ， 根据与其他用户的日期重叠情况 ， 为每个用户返回true/false 。 例如 ， 如果user1的订阅周期与其他任何用户重叠 ， 则查询必须为user1返回true 。
搞定SQL！5个棘手SQL查询的解决方法
本文插图
· 本文解决方案
经过初步分析 ， 我们可以知道必须将每项订阅与其他订阅进行比较 。 将userA的开始和结束日期视为startA 和endA ， 类似地 ， userB也依此设为startB和endB 。 如果startA≤endB且endA≥startB ， 则可以说这两个日期范围重叠 。 我们来举两个例子 ， 先比较一下U1和U3：
startA = 2020–01–01 endA = 2020–01–31 startB = 2020–01–16 endB = 2020–01–26
这里可以看出 ， startA(2020–01–01)小于endB(2020–01–26) ， 那么同样 ， endA(2020–01–31)大于 startB(2020–01–16) ， 因此可以得出结论 ， 日期重叠 。 类似地 ， 如果比较U1和U4 ， 上述条件就不成立 ， 于是返回FALSE 。
这里还必须确保不会将用户与其自己的订阅进行比较 。 同时希望运行一个左连接 ， 能够自行将用户与满足条件的其他用户进行匹配 。 现在 ， 我们将创建同一表的两个副本S1和S2 。
SELECT * FROM subscriptions AS s1 LEFT JOIN subscriptions AS s2 ON s1.user_id != s2.user_id AND s1.start_date <=s2.end_date AND s1.end_date >=s2.start_date
给定条件连接 ， 在日期之间存在重叠的情况下 ， 对于S1中的每个user_id ， 应该存在来自S2的user_id 。
· 输出
搞定SQL！5个棘手SQL查询的解决方法
本文插图
可以看到 ， 以防日期重叠 ， 每个用户都有一个对应用户 。 对于user1 ， 有2行显示其与2个用户相匹配 。 对于用户4 ， 对应的ID为空 ， 表示他与其他任何用户都不匹配 。 现在 ， 将其全部组合在一起 ， 按照s1.user_ID字段进行分组 ， 并检查s2.user_ID不为空的用户的值是否为真 。
· 最终查询
SELECT s1.user_id , (CASE WHEN s2.user_idIS NOT NULL THEN 1 ELSE 0 END) AS overlap FROM subscriptions AS s1 LEFT JOIN subscriptions AS s2 ON s1.user_id != s2.user_id AND s1.start_date <=s2.end_date AND s1.end_date >=s2.start_date GROUP BY s1.user_id


推荐阅读


上一篇：富丰财经|为什么中国富豪还去美国建厂？，人工费高中国几倍

下一篇：新华网|高端白酒涨价、“办事蟹卡”畅销……从纪委监委点名看双节“四风”问题新动向