在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
...    

我需要从"；name"；列，其中所有行的连续差异(以秒为单位(；time_ amp；列等于某个值。使用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"的调用中参数计数不正确

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

Answer 1

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

通常的解决方案是在where子句中复制visit_gap表达式，给出以下内容：

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()或visit_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;

仔细解释所有中间步骤！