LeetCode（数据库）- 每次访问的交易次数

秒速五厘米 2022-09-11 05:24 190阅读 0赞

**题目链接：**[点击打开链接][Link 1]

**题目大意：**略。

**解题思路：**注意解决方案(1)中，生成序数表可以借助于窗口函数 ROW\_NUMBER() OVER()；本题中如果采用解决方案(2)，在第 15 个测试用例中，会出现交易表为空的情况，所以在做表连接的时候处理下笛卡儿积为空的技巧。

**AC 代码**

-- 解决方案(1)
    SELECT *
    FROM
    (
        SELECT t5.rnb AS transactions_count, IFNULL(visits_count, 0) AS visits_count
        FROM
        (
            SELECT 0 AS rnb
            UNION
            SELECT ROW_NUMBER() OVER () AS rnb
            FROM Transactions
        ) t5
        LEFT JOIN
        (
            SELECT
                cnt AS transactions_count
                ,COUNT(user_id) AS visits_count
            FROM
            (
                SELECT t1.user_id, COUNT(t2.amount) AS cnt
                FROM Visits t1
                LEFT JOIN Transactions t2
                ON t1.user_id = t2.user_id AND t1.visit_date = t2.transaction_date
                GROUP BY user_id, visit_date
            ) t3
            GROUP BY cnt
        ) t4
        ON t5.rnb = t4.transactions_count
    ) t6
    WHERE transactions_count <= (
        SELECT COUNT(t2.amount) AS cnt
        FROM Visits t1
        LEFT JOIN Transactions t2
        ON t1.user_id = t2.user_id AND t1.visit_date = t2.transaction_date
        GROUP BY t1.user_id, visit_date
        ORDER BY cnt DESC
        LIMIT 1)
    ;
    
    -- 解决方案(2)
    # 计算每个用户每日期的交易次数
    WITH t1 AS(SELECT user_id, transaction_date, COUNT(*) cnt FROM Transactions GROUP BY user_id, transaction_date),
    
    # 计算每种交易次数对应的用户数(包含交易次数为 0 的情况)
    t2 AS(SELECT 0 transactions_count, SUM(IF(t1.cnt IS NULL, 1, 0)) visits_count
    FROM Visits v LEFT JOIN t1 ON v.user_id = t1.user_id AND v.Visit_date = t1.transaction_date
    
    UNION ALL
    
    SELECT cnt, COUNT(*) FROM t1 GROUP BY cnt),
    
    # 生成一张最多 Transactions.length 条记录的序数表
    t3 AS(SELECT CAST(@i := @i + 1 AS SIGNED) transactions_count, maxn
    FROM (SELECT @i:=-1, MAX(transactions_count) maxn FROM t2) init LEFT JOIN Transactions ON 1 = 1)
    
    # 为中途没有的交易次数种类填充
    SELECT t3.transactions_count, IFNULL(t2.visits_count, 0) visits_count
    FROM t3 LEFT JOIN t2 ON t3.transactions_count = t2.transactions_count
    WHERE t3.transactions_count <= maxn

[Link 1]: https://leetcode-cn.com/problems/all-people-report-to-the-given-manager/