普通会员 登录
题库 实习信息 校招公告 参访活动
题库 软件开发 题目列表 某App的event_table表记录了用户的事件数据：...
多选题
某App的event_table表记录了用户的事件数据： 
+----+---------+----------------+---------------------+
| id | user_id | event_name     | event_time          |
+----+---------+----------------+---------------------+
|  1 | 1001    | live_room      | 2023-03-14 21:01:56 |
|  2 | 1002    | home_page      | 2023-03-14 21:02:45 |
|  3 | 1001    | add_cart       | 2023-03-14 21:02:55 |
|  4 | 1002    | product_detail | 2023-03-14 21:04:48 |
|  5 | 1001    | pay            | 2023-03-14 21:05:57 |
|  6 | 1002    | live_room      | 2023-03-14 21:06:22 |
|  7 | 1002    | product_detail | 2023-03-14 21:06:35 |
|  8 | 1002    | add_cart       | 2023-03-14 21:06:46 |
|  9 | 1002    | pay            | 2023-03-14 21:06:51 |
| 10 | 1003    | live_room      | 2023-03-14 21:07:38 |
| 11 | 1003    | add_cart       | 2023-03-14 21:07:43 |
| 12 | 1003    | pay            | 2023-03-17 21:07:47 |
| 13 | 1004    | live_room      | 2023-03-14 21:32:35 |
| 14 | 1004    | add_cart       | 2023-03-14 21:32:41 |
| 15 | 1004    | product_detail | 2023-03-14 21:32:53 |
| 16 | 1004    | pay            | 2023-03-14 21:32:58 |
| 17 | 1005    | live_room      | 2023-03-14 21:33:45 |
| 18 | 1005    | add_cart       | 2023-03-14 21:34:15 |
| 19 | 1005    | product_detail | 2023-03-14 21:34:31 |
| 20 | 1005    | live_room      | 2023-03-14 21:34:35 |
| 21 | 1005    | add_cart       | 2023-03-14 21:34:38 |
| 22 | 1005    | product_detail | 2023-03-14 21:34:46 |
| 23 | 1005    | pay            | 2023-03-14 21:34:51 |
| 24 | 1006    | pay            | 2023-03-14 22:13:36 |
| 25 | 1006    | live_room      | 2023-03-14 22:13:45 |
| 26 | 1006    | add_cart       | 2023-03-14 22:13:57 |
+----+---------+----------------+---------------------+
用户的事件先是live_room，然后紧跟着add_cart（中间没有别的事件），之后紧跟是pay（与add_cart的时间差要小于等于48小时，中间允许有别的事件），才认为是直播引导的订单，比如表中只有用户1001、1004、1005的路径均符合（注意：1005的pay只能算一次，与最近一次add_cart相匹配）。想查出直播引导订单的明细，以下查询语句符合预期的是（）？
A.
SELECT t1.user_id, t1.event_name AS event_name1, t1.event_time AS event_time1, t2.event_name AS event_name2, t2.event_time AS event_time2
    , t3.event_name AS event_name3, t3.event_time AS event_time3
FROM (
    SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
    FROM event_table
) t1
    JOIN (
        SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
        FROM event_table
    ) t2
    ON t1.user_id = t2.user_id
    JOIN (
        SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
        FROM event_table
    ) t3
    ON t1.user_id = t3.user_id
WHERE t1.event_name = 'live_room'
    AND t2.event_name = 'add_cart'
    AND t3.event_name = 'pay'
    AND t1.rn + 1 = t2.rn
    AND t3.rn > t2.rn
    AND TIMESTAMPDIFF(HOUR, t2.event_time, t3.event_time) <= 48;
B.
SELECT *
FROM (
    SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time2 DESC) AS rn
    FROM (
        SELECT t1.user_id, t1.event_name AS event_name1, t1.event_time AS event_time1, t2.event_name AS event_name2, t2.event_time AS event_time2
            , t3.event_name AS event_name3, t3.event_time AS event_time3
        FROM (
            SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
            FROM event_table
        ) t1
            JOIN (
                SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
                FROM event_table
            ) t2
            ON t1.user_id = t2.user_id
            JOIN (
                SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
                FROM event_table
            ) t3
            ON t1.user_id = t3.user_id
        WHERE t1.event_name = 'live_room'
            AND t2.event_name = 'add_cart'
            AND t3.event_name = 'pay'
            AND t1.rn + 1 = t2.rn
            AND t3.rn > t2.rn
            AND TIMESTAMPDIFF(HOUR, t2.event_time, t3.event_time) <= 48
    ) t4
) t5
WHERE rn = 1;
C.
SELECT *
FROM (
    SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time2 DESC) AS rn
    FROM (
        SELECT t1.user_id, t1.event_name AS event_name1, t1.event_time AS event_time1, t2.event_name AS event_name2, t2.event_time AS event_time2
            , t3.event_name AS event_name3, t3.event_time AS event_time3
        FROM (
            SELECT *
            FROM (
                SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
                FROM event_table
            ) t0
            WHERE event_name = 'live_room'
        ) t1
            JOIN (
                SELECT *
                FROM (
                    SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
                    FROM event_table
                ) t0
                WHERE event_name = 'add_cart'
            ) t2
            ON t1.user_id = t2.user_id
            JOIN (
                SELECT *
                FROM (
                    SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
                    FROM event_table
                ) t0
                WHERE event_name = 'pay'
            ) t3
            ON t1.user_id = t3.user_id
        WHERE t1.rn + 1 = t2.rn
            AND t3.rn > t2.rn
            AND TIMESTAMPDIFF(HOUR, t2.event_time, t3.event_time) <= 48
    ) t4
) t5
WHERE rn = 1;
D.
SELECT t1.user_id, t1.event_name AS event_name1, t1.event_time AS event_time1, t2.event_name AS event_name2, t2.event_time AS event_time2
    , t3.event_name AS event_name3, t3.event_time AS event_time3
FROM (
    SELECT *
    FROM (
        SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
        FROM event_table
    ) t0
    WHERE event_name = 'live_room'
) t1
    JOIN (
        SELECT *
        FROM (
            SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
            FROM event_table
        ) t0
        WHERE event_name = 'add_cart'
    ) t2
    ON t1.user_id = t2.user_id
    JOIN (
        SELECT *
        FROM (
            SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
            FROM event_table
        ) t0
        WHERE event_name = 'pay'
    ) t3
    ON t1.user_id = t3.user_id
WHERE t1.rn + 1 = t2.rn
    AND t3.rn > t2.rn
    AND TIMESTAMPDIFF(HOUR, t2.event_time, t3.event_time) <= 48;
确定
上一题
没有了
下一题
纠错
题目信息
校招真题
0%
正确率
0
评论
317
点击
收藏 已收藏
错题本 已加入错题本
我的笔记
登录添加笔记