sql - 在 SQL 中的表中选择具有受限时间戳差异（以秒为单位）的行

Question

我在 SQL 中有一个名为 visit_times 的表，如下所示

    name   time_stamp
    Allen  2015-02-13 07:10:54
    Allen  2015-02-13 07:10:58
    Allen  2015-02-13 07:11:02
    Mary   2015-02-17 10:45:33
    Mary   2015-02-17 10:45:39
    Mary   2015-02-17 10:45:43
    ...    

我需要从“名称”列中选择名称，其中“time_stamp”列中的所有行连续差异（以秒为单位）都等于某个值。使用 LAG() 命令，我尝试将其编码如下

WITH cte AS
(
  SELECT  name, 
          DATEDIFF(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap 
  FROM customer_transactions
)
SELECT cte.name
FROM cte
GROUP BY cte.name
HAVING MIN(cte.visit_gap) = 10 AND MAX(cte.visit_gap) = 4;

我希望得到如下结果：

---------
| name  |
---------
| Allen |
---------

但它什么也没输出！我收到错误：在预先编写的模板中：调用本机函数“DATEDIFF”时参数计数不正确

我不知道如何解决这个问题。任何提示将不胜感激。

标签： sqlsql-serversql-timestamp

Answer 1

SQL 查询是按特定顺序处理的（快速搜索“sql 查询操作顺序”给了我这个不错的结果）。列别名visit_gap只能从order by子句开始重用。这解释了您的语法错误。

通常的解决方案是复制子句visit_gap中的表达式，为您提供：where

SELECT  name, 
        time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) AS visit_gap
FROM visit_times
WHERE time_stamp - LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp) = 4;

但是，这会给您一个新错误，指出该LAG()函数不能出现在where子句中......

窗口函数只能出现在 SELECT 或 ORDER BY 子句中。

为了将LAG()orvisit_gap计算和过滤（where子句）分开，您可以使用公用表表达式(CTE)。此外，使用DATEDIFF()函数（函数文档）来计算日期之间的差异。

with cte as
(
  SELECT  name, 
          datediff(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap
  FROM visit_times
)
select cte.name,
       cte.visit_gap --> column alias is available now!
from cte;

在 where 子句中添加过滤器会给你最终的结果：

with cte as
(
  SELECT  name, 
          datediff(second, LAG(time_stamp) OVER (PARTITION BY name ORDER BY time_stamp), time_stamp) AS visit_gap
  FROM visit_times
)
select cte.name,
       cte.visit_gap --> column alias is available now!
from cte
where cte.visit_gap > 4;

摆弄所有解释的中间步骤！