database - PostgreSQL - 根据条件为每一行分配值

Question

我有下表。

date    label   time2014-04-06  A   12:05:562014-04-06  A   23:02:322014-04-06  B   8:39:252014-04-06  B   12:36:372014-04-06  C   12:20:432014-04-06  C   12:56:442014-04-06  D   20:52:222014-04-06  E   22:25:302014-04-06  F   12:16:152014-04-06  F   12:31:092014-04-06  F   17:12:062014-04-06  G   7:48:322014-04-06  H   17:58:112014-04-05  I   12:05:562014-04-05  I   20:02:322014-04-05  I   23:57:322014-04-05  J   12:36:372014-04-05  K   12:20:432014-04-05  L   12:56:442014-04-05  M   20:52:222014-04-05  M   22:25:302014-04-05  N   12:16:152014-04-05  O   12:31:092014-04-05  O   17:12:062014-04-05  P   7:48:322014-04-05  Q   17:58:112014-04-06  R   1:05:562014-04-06  R   5:02:322014-04-06  R   8:39:252014-04-06  R   12:36:372014-04-06  S   12:20:432014-04-06  S   12:56:442014-04-06  S   20:52:222014-04-06  T   22:25:302014-04-06  U   12:16:152014-04-06  V   12:31:092014-04-06  W   3:48:322014-04-06  W   7:48:322014-04-06  W   17:58:11

我正在尝试使用此输出创建 View 。

date    label   time    status2014-04-06  A   12:05:56    Repeat2014-04-06  A   23:02:32    Unique2014-04-06  B   8:39:25 Repeat2014-04-06  B   12:36:37    Unique2014-04-06  C   12:20:43    Repeat2014-04-06  C   12:56:44    Unique2014-04-06  D   20:52:22    Unique2014-04-06  E   22:25:30    Unique2014-04-06  F   12:16:15    Repeat2014-04-06  F   12:31:09    Repeat2014-04-06  F   17:12:06    Unique2014-04-06  G   7:48:32 Unique2014-04-06  H   17:58:11    Unique2014-04-05  I   12:05:56    Repeat2014-04-05  I   20:02:32    Repeat2014-04-05  I   23:57:32    Unique2014-04-05  J   12:36:37    Unique2014-04-05  K   12:20:43    Unique2014-04-05  L   12:56:44    Unique2014-04-05  M   20:52:22    Repeat2014-04-05  M   22:25:30    Unique2014-04-05  N   12:16:15    Unique2014-04-05  O   12:31:09    Repeat2014-04-05  O   17:12:06    Unique2014-04-05  P   7:48:32 Unique2014-04-05  Q   17:58:11    Unique2014-04-06  R   1:05:56 Repeat2014-04-06  R   5:02:32 Repeat2014-04-06  R   8:39:25 Repeat2014-04-06  R   12:36:37    Unique2014-04-06  S   12:20:43    Repeat2014-04-06  S   12:56:44    Repeat2014-04-06  S   20:52:22    Unique2014-04-06  T   22:25:30    Unique2014-04-06  U   12:16:15    Unique2014-04-06  V   12:31:09    Unique2014-04-06  W   3:48:32 Repeat2014-04-06  W   7:48:32 Repeat2014-04-06  W   17:58:11    Unique

status 列的条件将如下所示。

我想根据派生状态列的 label 和 time 列条件在每一行中循环。

假设第 1 行标签 = 第 2 行标签，并且第 2 行和第 1 行的时间差大于 24:00:00 那么它必须是 yes 否则 no。

我在 excel 中这样做。

=IF(AND(B2=B3,C3-C2>1),"是","否")

我是 PostgreSQL 和数据库的新手。

任何建议或帮助都将对通过此过程非常有帮助。

提前致谢。

Answer 1

注意事项:

1. 如果您的公式在 Excel 中确实有效，那么您在单元格中存储的是日期，而不是时间。
2. 对于 D, E，我不明白，当前一行没有相同的标签时，这应该如何返回"is"
3. 您必须向表中添加一些带有 ID 的列 (!)。虽然 Excel 在工作表中保持相同的行顺序(除非您明确更改它)，但 PostgreSQL 不会。因此，如果您真的只有列时间中的时间，那么您将无法获得与表中相同的行顺序，从而导致完全不正确的结果。
4. 如果您使用的是 8.4 版，那么您的链接是正确的，但是如果您使用最新的 documentation 会更好

数据:

drop table if exists tmp.test;

create table tmp.test (id int, ddate date, label varchar, ttime time);

insert into tmp.test values

(1, '2014/6/4','A','12:05:56'),
(2, '2014/6/4','A','23:02:32'),
(3, '2014/6/4','B','8:39:25'),
(4, '2014/6/4','B','12:36:37'),
(5, '2014/6/4','C','12:20:43'),
(6, '2014/6/4','C','12:56:44'),
(7, '2014/6/4','D','20:52:22'),
(8, '2014/6/4','E','22:25:30'),
(9, '2014/6/4','F','12:16:15'),
(10, '2014/6/4','F','12:31:09'),
(11, '2014/6/4','F','7:12:06'),
(12, '2014/6/4','G','7:48:32'),
(13, '2014/6/4','H','17:58:11');

查询:

select
  id, 
  ddate,
  label,
  ttime,
  case when (lag(ttime) over(partition by label order by id))::interval
        + ttime::interval > interval '24 hours' then 'yes' else 'no' end
  -- ,(lag(ttime) over(partition by label order by ttime))::interval + ttime::interval
from
  tmp.test

解释:

1. lag 函数将获取给定分区的前一行中的值。在我们的例子中，分区由标签定义。
2. cast运算符::会将time类型变为interval，这样我们就可以加上时间，得到超过24小时。
3. 我们将总数与 24 小时间隔进行比较，并显示一个漂亮的标签 yes 或 no。

更新:

select
  id, 
  ddate,
  label,
  ttime,
  case when lead(label) over(partition by label order by id) is null then 'no' else 'yes' end
from
  tmp.test