我有这个SQL查询:
SELECT COUNT(DISTINCT CustomerName) over(
ORDER BY OrderTimestamp
RANGE BETWEEN INTERVAL 2 hour PRECEDING AND CURRENT ROW
) AS count_per_time
FROM Orders
WHERE CustomerName IS NOT null
AND CustomerName != ''
但它不起作用，因为DISTINCT不允许与OVER子句一起使用.请问有谁能帮我解题吗？非常感谢.
我不认为这可以用窗口函数来解决.
有一种仿真技术使用dense_rank的减法，但后者不支持窗口框架规范(即:原始代码中的range/rows语法).准确地说，它实际上允许语法，但却悄悄地忽略了它:所以它仍然在整个分区上操作.这一点在the documentation节中有解释:
标准SQL指定在整个分区上操作的窗口函数不应该有FRAME子句.MySQL允许为这类函数使用FRAME子句，但会忽略它.These functions use the entire partition even if a frame is specified:
CUME_DIST() DENSE_RANK() LAG() LEAD() NTILE() PERCENT_RANK() RANK() ROW_NUMBER()
似乎剩下的唯一选项就是子查询:
SELECT o.*,
(
SELECT COUNT(DISTINCT o1.CustomerName)
FROM Orders o1
WHERE o1.CustomerName != ''
WHERE o1.OrderTimestamp BETWEEN o.OrderTimestamp - INTERVAL 2 HOUR AND o.OrderTimestamp
) AS count_per_time
FROM Orders o
WHERE o.CustomerName IS NOT null and o.CustomerName != ''