主页 / dba / 问题 / 291777
Accepted
Daniel Müller
Asked: 2021-05-19 04:35:20 +0800 CST

在SQL中获取具有特定值的行之前的行

  • 772

我有一个如下表，其中用户操作与时间戳一起存储。我的目标是识别在特定操作（名为reference_action）之前发生的操作并计算这些操作的数量以查看哪些操作在特定操作之前发生以及它们是如何分布的。

我知道像 LAG() 这样的窗口函数，我可以在其中获取某一行之前的行，但不知道如何包含像WHERE action_name = "reference_action".

查询引擎是 Presto，表是 Hive 表，但我最感兴趣的是一般的 SQL 方法，因此这无关紧要。

会议 动作名称 时间戳
1 “一些动作” 1970-01-01 00:01:00
1 “一些动作” 1970-01-01 00:02:00
1 “一些动作” 1970-01-01 00:03:00
1 “desired_action1” 1970-01-01 00:04:00
1 “reference_action” 1970-01-01 00:05:00
1 “一些动作” 1970-01-01 00:06:00
1 “一些动作” 1970-01-01 00:07:00
2 “一些动作” 1970-01-01 01:23:00
2 “一些动作” 1970-01-01 02:34:00
2 “desired_action1” 1970-01-01 03:45:00
2 “reference_action” 1970-01-01 04:56:00
2 “一些动作” 1970-01-01 05:58:00
3 “一些动作” 1970-01-01 01:23:00
3 “一些动作” 1970-01-01 02:34:00
3 “desired_action2” 1970-01-01 03:45:00
3 “reference_action” 1970-01-01 04:56:00
3 “一些动作” 1970-01-01 05:58:00

结果应如下所示：

行动 数数
“desired_action1” 2
“desired_action2” 1

有两行，其中“desired_action1”直接跟在“reference_action”行之后，当按 排序时timestamp，因此计数为 2。同样的逻辑适用于为什么“desired_action2”的计数为 1。

目标是了解用户在购买之前做了什么（购买 = reference_action）。要了解他之前做了什么，我想查找购买之前发生的操作。因此，我需要在reference_action 之前知道行中的action_name。required_actions 必须被计算，reference_actions 只是我想要计算的操作之后的行，用于确定应该计算哪些值。

sql-standard window-functions

1 个回答

  1. Best Answer

    在任何支持的 SQL 变体中，执行此操作的步骤实际上都非常简单LAG：

    • 在派生表或 CTE 中提取先前的action_nameusing ,。LAG
    • 在外部过滤到仅包含 的行'reference_action'，这些行也将具有第一步中的先前操作。您想在外面执行此操作，否则您将无法获得正确的LAG值。
    • 按上一个动作分组，并计数。
    SELECT
    prevAction AS action,
    COUNT(*) AS [count]
FROM (
    SELECT *,
        LAG(action_name) OVER (ORDER BY timestamp) AS prevAction -- you may want a PARTITION also
    FROM YourTable
) AS t
WHERE action_name = 'reference_action'
GROUP BY prevAction;
    • 2

相关问题