rows between unbounded preceding and current row使用方法
时间: 2024-05-02 18:17:33 浏览: 8
"ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 是 SQL 中的一个窗口函数(Window Function)的语法,用于指定窗口的范围。它的含义是从第一行到当前行的范围内进行计算。
具体来说,如果一个查询包含窗口函数,那么这个窗口函数会按照指定的窗口范围(也就是 "ROWS BETWEEN" 子句)在每一行上进行计算。"UNBOUNDED PRECEDING" 表示从窗口的第一行开始计算,而 "CURRENT ROW" 则表示当前行。
例如,以下是一个使用 "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 的例子:
```
SELECT
name,
age,
salary,
SUM(salary) OVER (ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM
employees
ORDER BY
age;
```
在这个例子中,我们使用 "SUM" 窗口函数计算每个员工的薪水累计总和,并使用 "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 来指定窗口范围。这个查询会按照员工年龄升序排序,并计算每个员工的薪水累计总和。
相关问题
sum(d.printed_number) over( partition by substring(c.month, 1, 4), c.model_code order by c.month rows between unbounded preceding and current row ) as pn_YTM
这是一个 SQL 查询语句,其中使用了窗口函数 sum() over,对 printed_number 列进行求和。窗口中使用了 partition by 子句对数据进行分区按照指定的排序对数据进行排序。rows between 子句指定了窗口函数计算的范围,这里是从当前行往前计算到分区的第一行。最后,as 子句为本次查询结果命名为 pn_YTM。
ClickHouse exception, code: 215, host: 123.249.3.11, port: 8123; Code: 215, e.displayText() = DB::Exception: Column `sales` is not under aggregate function and not in GROUP BY: While processing period_code, formatDateTime(parseDateTimeBestEffort(period_start), '%Y') AS years, product_name AS product, sum(sales), sum(sales) OVER (PARTITION BY years ORDER BY period_code DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sale (version 21.3.4.25)
这个异常提示指出了问题所在:`Column 'sales' is not under aggregate function and not in GROUP BY`。这意味着你的查询中包含了未被聚合函数或 GROUP BY 子句包含的列。
同时,你在查询中也使用了窗口函数 `sum(sales) OVER (PARTITION BY years ORDER BY period_code DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sale`,这可能会导致 ClickHouse 引擎无法正确处理这个查询。
要解决这个问题,你需要将所有未被聚合函数或 GROUP BY 子句包含的列都包含在窗口函数中,或者将窗口函数和聚合函数分别使用。以下是一个修改后的示例:
```
SELECT formatDateTime(parseDateTimeBestEffort(period_start), '%Y') AS years,
product_name AS product,
sum(sales) AS total_sales,
sum(sales) OVER (PARTITION BY formatDateTime(parseDateTimeBestEffort(period_start), '%Y') ORDER BY period_code DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_sales
FROM your_table
GROUP BY years, product;
```
在这个查询中,我们将 `formatDateTime(parseDateTimeBestEffort(period_start), '%Y')` 列包含在了窗口函数中,并使用了 GROUP BY 子句。这样,你就可以正确地计算每个产品每年的销售总额和累计销售总额了。