代码之家 › 专栏 › 技术社区 › LittleBobbyTables

窗口函数,用于选择红移Postgres中多数行的名称

window-functions amazon-redshift sql

LittleBobbyTables · 技术社区 · 7 年前

create table pages (
  page varchar,
  cat varchar,
  hits int
);

insert into pages values
(1, 'asdf', 1),
(1, 'fdsa', 2),
(1, 'Apples', 321),
(2, 'gwegr', 30),
(2, 'hsgsdf', 2),
(2, 'Bananas', 321);

我想知道每个页面的正确类别,以及总点击量。正确的类别是点击率最高的类别。我想要一个数据集,如:

page | category | sum_of_hits
-----------------------------
1    | Apples   |  324
2    | Bananas  |  353

SELECT page,
       last_value(cat) over (partition BY page ORDER BY hits) as category,
       sum(hits) as sum_of_hits
FROM pages
GROUP BY 1, 2

但这是错误的: ERROR: column "pages.hits" must appear in the GROUP BY clause or be used in an aggregate function Position: 83

我试着把点击数汇总起来- ORDER BY max(hits) 但这没有道理,也不是我想要的。

小提琴: http://sqlfiddle.com/#!17/cb3c2/17

4 回复 | 直到 7 年前

cha 7 年前

Postgres documentation 关于最后一个值:

“窗口框架”中的行,默认情况下包含这些行从分区开始到当前一行这可能会对第n_值和尤其是last_值。可以将框架重新定义为整体通过在无界前置和无界之间添加行进行分区

我建议您将其转换为first_值:

SELECT page,
       first_value(cat) over (partition BY page ORDER BY hits DESC) as category,
       hits
FROM pages

select page, category,
       sum(hits)
from (
SELECT page,
       first_value(cat) over (partition BY page ORDER BY hits DESC) as category,
       hits
FROM pages
) a
GROUP BY 1, 2

SQL Fiddle

klin 7 年前

使用窗口功能 first_value() hits 在派生表(FROM子句中的子查询)中:

select 
    page, 
    category,
    sum(hits) as sum_of_hits
from (
    select
        page,
        first_value(cat) over (partition by page order by hits desc) as category,
        hits
    from pages
    ) s
group by 1, 2
order by 1;

SqlFiddle.

cowbert 7 年前

select page, cat, hits from
    (select page, cat, hits 
     ,max(hits) over (partition by page) as m_hits 
     from pages) t
where m_hits = hits

Gordon Linoff 7 年前

你似乎想得到点击总数和

select page, sum(hits) as total_hits,
       max(case when seqnum = 1 then category end) as category
from (select p.*,
             row_number() over (partition by page order by hits desc) as seqnum
      from pages p
     ) p
group by page;