mysql/postgres窗口函数无子查询限制结果

wkyowqbh  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(412)

有没有可能在没有子查询的情况下,通过分区限制窗口函数的结果?这段代码在postgres/mysql中。我在寻找mysql和postgres的解决方案。
例如:假设连接与问题的重点无关。

select acct.name, we.channel, count(*) as cnt,
    max(count(*)) over (partition by name order by count(*) desc) as max_cnt
from web_events we join accounts acct
    on we.account_id=acct.id
group by acct.name, we.channel
order by name, max_cnt desc;

此查询的结果给出:

我只想显示每个窗口分区的第一行。例如:带有cnt的行:[3m,19],[abbott labtories,20]
我尝试了以下不起作用的操作(在窗口函数中添加了限制1):

select acct.name, we.channel, count(*) as cnt,
        max(count(*)) over (partition by name order by count(*) desc limit 1) as max_cnt
    from web_events we join accounts acct
        on we.account_id=acct.id
    group by acct.name, we.channel
    order by name, max_cnt desc;
m2xkgtsf

m2xkgtsf1#

我只想显示每个窗口分区的第一行。例如:带有cnt的行:[3m,19],[abbott labtories,20]
实际上这里不需要窗口函数,因为第一行 max_cnt 永远平等 cnt . 而是使用 DISTINCT ON 结合 GROUP BY .
来自postgresql文档
select distinct on(表达式[,…])只保留给定表达式计算结果相等的每组行的第一行。distinct on表达式使用与order by相同的规则进行解释(见上文)。请注意,每个集合的“第一行”是不可预测的,除非使用order by来确保所需的行首先出现

SELECT DISTINCT ON(acct.name) 
  acct.name
, we.channel
, COUNT(*) cnt
FROM web_events we 
JOIN accounts acct
  ON we.account_id=acct.id
GROUP BY 1, 2
ORDER BY name, cnt DESC;

这里有一个sqlfiddle的快速演示。http://sqlfiddle.com/#!17/57694/8
我刚开始用的时候总是搞砸 DISTINCT ON 是为了确保 ORDER BY 子句以 DISTINCT ON . 在上面的例子中 ORDER BY 开始于 acct.name 如果第一个位置有平局,则返回符合条件的第一行。这是不确定的。可以在中指定其他表达式 ORDER BY 以影响在此设置中返回的行。
例子:

ORDER BY name, cnt DESC, channel = 'direct'

将返回包含 facebook ,如果为给定帐户,则 facebook 以及 direct 产量相同 cnt .
但是,请注意,使用这种方法,不可能返回为第一个位置绑定的所有行,即两行都包含 facebook & direct (不使用子查询)。 DISTINCT ON 可以与组合在同一语句中 GROUP BY s(上例)和 WINDOW FUNCTIONS (示例如下)。这个 DISTINCT ON 子句在 LIMIT .
例如,下面的查询(尽管毫无意义)显示了 DISTINCT ONWINDOW FUNCTION . 它将在每个 max_cnt ```
SELECT DISTINCT ON(mxcnt)
acct.name
, we.channel
, COUNT() cnt
, MAX(COUNT(
)) OVER (PARTITION BY acct.name) mxcnt
FROM web_events we
JOIN accounts acct
ON we.account_id=acct.id
GROUP BY 1, 2
ORDER BY mxcnt, cnt DESC;

9gm1akwq

9gm1akwq2#

使用子查询。如果您只想要一行(即使有领带),那么使用 row_number() :

select name, channel, cnt
from (select acct.name, we.channel, count(*) as cnt,
             row_number() over (partition by acct.name order by count(*) desc) as seqnum
      from web_events we join
           accounts acct
           on we.account_id = acct.id
      group by acct.name, we.channel
     ) wea
order by name;

你可以用 rank() 如果您希望一个帐户有多个行,则在发生关系时。

相关问题