sql - 如何将计数列添加到查询中？

Question

我有一个问题

WITH a as (
SELECT
      SECTION
    , MAX(PRICE) over w
    , MIN(PRICE) over w
    , AVG(PRICE) over w
    , TIME t, price
    , case when MAX(PRICE) over w = price then TIME end maxt
    , case when MIN(PRICE) over w = price then TIME end mint
FROM s154
WINDOW w as (partition by section)
)
select DISTINCT
      SECTION
    , MAX
    , MIN
    , AVG
    , max(maxt) over (partition by section)
    , min(mint) over (partition by section)
from a;

我决定通过向 WITH 添加新列来修改我的表:

count(*) FROM s154 GROUP BY section.

但是添加 group by 子句也会要求 group by 中的最大值和最小值。是否可以对查询的 WITH 部分中的部分进行计数？

Answer 1

你可以在 w 上添加 count(*):

WITH s as (
      SELECT SECTION, MAX(PRICE) over w as max_price,
             MIN(PRICE) over w as min_price, AVG(PRICE) over w as avg_price, 
             TIME as t, price,
             (case when MAX(PRICE) over w = price then TIME end) as maxt
             (case when MIN(PRICE) over w = price then TIME end) as mint,
             (COUNT(*) over w) as cnt
      FROM s154 WINDOW w as (partition by section)
     )
select DISTINCT SECTION, max_price, min_price, avg_price,
       max(maxt) over (partition by section),
       min(mint) over (partition by section),
       cnt
from s;

我很确定这个查询可以简化。我添加了一些内容以便更容易理解:

• 明确的列别名。为您自己的列命名，它们很重要。
• as 在列别名之前，这样我就可以知道名称在哪里。
• 有意义的 CTE 名称。 “a”在这种情况下是没有意义的。至少“s”是表格的缩写。

我认为更简单的版本是:

      SELECT SECTION, MAX(PRICE) as max_price,
             MIN(PRICE) as min_price, AVG(PRICE) as avg_price, 
             (ARRAY_AGG(time ORDER BY price))[1] as time_at_min_price,
             (ARRAY_AGG(time ORDER BY price DESC))[1] as time_at_max_price
      FROM s154 
      GROUP BY section;

这似乎是表达逻辑的更好方式。