时间戳

首页 > 解决方案 > 计算时间戳列的相等日期部分跨所述列的组

sql - 计算时间戳列的相等日期部分跨所述列的组

问题描述

我想计算重复的日期值。我已经知道我的字段“WHEN”是一个时间戳，所以我必须将它转换为日期类型。

我的实际查询如下所示：

SELECT 
    u.USERNAME,  
    r."WHEN",
    r.UPDATEINOUT,
    case (r.UPDATEINOUT) when 0 then  0 when 1 then 1 else r.INOUT end INOUT
FROM 
    ATTENDANT r 
LEFT JOIN  
    USERS u ON r.USERID = u.ID 
where 
    u.USERNAME = 'rk' and (r.UPDATEINOUT = 1 or r.UPDATEINOUT = 0 or r.UPDATEINOUT is null)
group by
    r."WHEN",
    INOUT,
    u.USERNAME,
    r.UPDATEINOUT
order by 
    r."WHEN"

这是结果：

Username     WHEN             UPDATEINOUT  INOUT

rk  09.04.2018, 14:59:45.000    [null]  0
rk  09.04.2018, 14:59:51.000    [null]  1
rk  11.04.2018, 08:31:02.000    [null]  0
rk  11.04.2018, 12:06:52.000    [null]  1
rk  11.04.2018, 12:10:29.000    [null]  0
rk  11.04.2018, 12:23:09.000    [null]  1
rk  11.04.2018, 12:43:47.000    [null]  0
rk  11.04.2018, 17:07:40.000    [null]  1

现在我想计算重复的日期，结果应该是这样的：

Username     WHEN             UPDATEINOUT  INOUT  Count

    rk  09.04.2018, 14:59:45.000    [null]  0       2
    rk  09.04.2018, 14:59:51.000    [null]  1       2
    rk  11.04.2018, 08:31:02.000    [null]  0       6
    rk  11.04.2018, 12:06:52.000    [null]  1       6
    rk  11.04.2018, 12:10:29.000    [null]  0       6
    rk  11.04.2018, 12:23:09.000    [null]  1       6
    rk  11.04.2018, 12:43:47.000    [null]  0       6
    rk  11.04.2018, 17:07:40.000    [null]  1       6

当我添加

count(cast(r."WHEN" as date))

然后它只显示一个。

标签： sqlcountfirebirdfirebird2.5

解决方案

似乎有一些答案建议“做什么”，但没有试图解释为什么结果会像现在这样。怎么了。似乎给主题启动器的是一条鱼，而不是一根钓鱼竿。

当我添加时count(cast(r."WHEN" as date))，它只显示一个。

这有点好笑。这条线显示了像英语这样的自然人类语言和像 SQL 这样的数学集合语言之间的新手误解。

R.Kut 将他添加的这一行读作“我想count知道有多少 [distinct] 值given expression”。

但这并不是该命令在 SQL 中的实际含义。在 SQL 中，它的意思是“我想count知道有多少行，其中given expression不为空”。

count(cast(r."WHEN" as date))因此， and之间实际上没有区别count(r."WHEN")- 这两个参数表达式是 bothNULL或 both NOT NULL。因此count，那些同样可以为空的参数的值也是相等的。

试图截断聚合函数的参数值，就像它可以改变可空性一样，是一种误解。我也在那里。需要时间来了解数学集合上的聚合的真正含义，以及在阅读 SQL 时不会阅读英语。

坦率地说，您不仅可以在count (1)这里删除类型转换，还可以删除列本身 - 它仍然是相同的，因为这些是行，而不是被要求计算的值。除非有行在哪里"WHEN" IS NULL- 将由group by但不由count. 在您阅读并思考完下一部分之后，回来玩弄http://sqlfiddle.com/#!9/ee09a/7

count现在，我在上面提到的函数还有另一个参数。就是那个“不同”的参数。

备注：有人可能会说distinct是 SQL 语言的关键字，不是函数的参数，而是事实上的，而不是法律上的，它改变了函数的工作方式，所以在我看来它是参数，以那种不寻常的 SQLish 方式参数通常被赋予函数。或者，另一种推理方式，它可能是函数名称的一部分，如果想象有两个函数可供选择，count和count-distinct。

因此，可以count(distinct cast(r."WHEN" as date))改为添加主题启动器，并且...

....看到没有任何改变。因为虽然这一次他真的会对服务器说用非空（总是只非空！）和distinct值来计算行 - 计数在组内。

这些群体是什么？

group by
    r."WHEN",
    INOUT,
    u.USERNAME,
    r.UPDATEINOUT

看，在各个组中，我们有具有不同时间和日期值“WHEN”的行。还有其他专栏，但我不关注它们。这里的问题是，在每个组中，“WHEN”的时间和日期部分都保持不变。而“相同”的意思是“有一个distinct值，一次又一次地重复”。如果只有一个distincttime-and-date 值，那么 only-time 或 only-date 的减少值将具有相同的计数（减少一个值只能使以前不同的值相等，但不能使以前相等的值现在不同）。

当然，在其他情况下，当计数超过未包含在组中的列时（或者根本没有group by子句时），结果可能会有所不同。count(distinct ...)可能会做主题启动者期望它做的事情。

http://sqlfiddle.com/#!9/0d65bf/7 - 示例。

但是，必须牢记：

  • 这是以服务器完成的额外排序和分组工作为代价的，可能会使查询变慢或消耗大量内存
  • 它仍然可以在组内工作（然后组将开始包含counted 列的不同值）。
  • ....有时组是整个查询结果集（最简单的变体 - 整个表），如果程序员没有通过添加group by子句将其设置为不同:-D

上面链接的示例代码：

create table X(a integer, b integer);

insert into X values (1,1);
insert into X values (1,2);
insert into X values (1,2);

commit;

select count(distinct b) from x group by a
-- Result: 1 row: 2

-- or if the whole table is the group
select count(distinct b) from x 
-- Result: 1 row: 2

-- but if the group includes the counted column
-- then every group would contain EXACTLY ONE
-- row with a not-null distinct value
select count(distinct b) from x group by b
-- Result: 2 rows: 1 and 1

在这里，我们对 toy with 进行了另一种修改。

group by
    cast(r."WHEN" as date),   -- <====
    INOUT,
    u.USERNAME,
    r.UPDATEINOUT

现在，这一次我们告诉服务器组装组，其中“WHEN”中只有“日期”部分相同，而“时间”部分可能不同。

然而....

  1. 我认为并非每个 SQL 服务器都支持group by子句中的表达式。
  2. 除非有一个index完全由该表达式创建的，否则服务器将不得不做很多额外的工作，可能会求助于自然扫描和临时文件分组，这会使查询变得繁重和缓慢。
  3. 当然，您必须相应地修改列列表。

.

SELECT 
    u.USERNAME,  
    cast(r."WHEN" as date),  -- <=== no more raw r."WHEN"
    r.UPDATEINOUT,

WHEN因为，好吧，如果您明确要求服务器对它的不同值进行分组，那么您就不能在行中拥有“一个真实值” 。

在这里，您可能会得出结论，没有简单直接的方法来同时拥有组和跨组聚合。或者换一种说法，在一个查询中有两组不同的集合group by（我不想在UNION这里提及，mkay？）。

您将需要一组group by标准来计算具有相同日期部分（但可能时间部分不同）的行，而另一group by组标准则用于选择和呈现因日期和时间而不同的组。

在简单明了的 SQL 1999 中，这意味着您必须制作 TWOselect才能以一种或另一种方式进行两个分组，而 Yogesh 和 Gordon 的答案就在这里。

就像 Gordon 在 99 后 SQL 中提到的那样，出现了 Window Functions，它可以让您将这些标准集合二为一select，但无论如何它们在 Firebird 2.x 中都不可用。

推荐阅读