sql - PostgreSQL:如何从数据输出中编辑数据？

Question

例如，在 excel 上，我可以简单地编写一个公式，从所选数据集中的另一个单元格中减去一个单元格，我正在努力基本上用 PostgreSQL 做同样的事情。

我有一个数据集，它提供了过去 28 个月消费者的事件模式。数据包含时间戳记录消费者何时何地，但不包含他在每个位置或事件中花费的时间。我试过使用 Age 函数，但由于我没有显示消费者何时离开该位置的列，所以我无法执行以下操作:

SELECT AGE(ended_at,virtual_time)
WHERE consumer_id = '93da'
FROM public.consumer

下面的代码为我提供了消费者的事件模式和 virtual_time。但我不知道如何添加另一列来计算每项事件花费的时间。

SELECT activity, virtual_time
FROM public.consumer
WHERE consumer_id = '93da'
GROUP BY 1,2
ORDER BY 2;

我试图通过某种方式使用时间戳记录来计算在每个事件上花费的时间，创建一个新列减去时间戳编号。 2 按时间戳 1。

例如，会做的事情:

   Virtual_Time            Activity      Time_Spent
1) 16/09/2017 17:52:06     Gym           00:34:11 
2) 16/09/2017 18:26:17     Whole Foods   00:05:22
3)...

花费的时间 = 时间戳 2 (16/09/2017 17:52:06) 减去时间戳 1 (16/09/2017 18:26:17) .反之亦然。

我什至可以做我想做的事吗？

@专线小巴:

您提供的代码为前 8 条记录返回了以下结果:

    Virtual_Time              Activity       Time_Spent
1) 2017-09-16 17:52:06     Marks & Spencer   00:00:00
2) 2017-09-16 17:52:06     Marks & Spencer   [NULL] 
3) 2017-09-16 18:26:17     Whole Foods       00:00:00
4) 2017-09-16 18:26:17     Whole Foods       00:34:11
5) 2017-09-16 18:26:19     Whole Foods       00:00:00
6) 2017-09-16 18:26:19     Whole Foods       00:00:02
7) 2017-09-16 18:26:20     Whole Foods       00:00:00
8) 2017-09-16 18:26:20     Whole Foods       00:00:01

不确定为什么会有 NULL 值，它几乎就像第 no 行。 4 计算的时间戳编号4 减去时间戳编号。 2. 但其余的都不对。并且有很多重复显示(例如第 3 和 4 行，第 5 和 6 行)。

非常感谢您的帮助。请告诉我您的想法。

Answer 1

看来你需要window function LAG() (从 Postgres 8.4 开始可用):

SELECT 
    virtual_time, 
    activity, 
    AGE(
        virtual_time, 
        LAG(virtual_time) OVER(PARTITION BY consumer_id ORDER BY virtual_time)
    ) Time_Spent
FROM public.consumer
WHERE consumer_id = '93da'
GROUP BY virtual_time, activity, consumer_id
ORDER BY virtual_time;

LAG() 允许您在给定排序条件的情况下访问分区(即一组记录)中当前记录之前的第 N 个(默认:第一个)记录。这里我们按客户划分，按虚拟时间排序。 GROUP BY 子句消除了来自原始数据的重复项。

这个 DB Fiddle demo 您的 8 条样本数据记录返回:

| virtual_time             | activity      | time_spent                  |
| ------------------------ | ------------- | --------------------------- |
| 2017-09-16T17:52:06.000Z | Marks&Spencer |                             |
| 2017-09-16T18:26:17.000Z | WholeFoods    | {"minutes":34,"seconds":11} |
| 2017-09-16T18:26:19.000Z | WholeFoods    | {"seconds":2}               |
| 2017-09-16T18:26:20.000Z | WholeFoods    | {"seconds":1}               |

注意:第一个记录不能花费时间，因为它没有可比较的先前记录。