带红移的 MODE 聚合函数 [英] MODE aggregation function with redshift

问题描述

我有以下表格:

客户

customer_id name
----------------
1           bob
2           alice
3           tim

购买

id customer_id item_bought
--------------------------
1  1           hat
2  1           shoes
3  2           glasses
3  2           glasses
4  2           book
5  3           shoes
6  1           hat

我想要以下结果:

customer_name item_bought_most_often
------------------------------------
bob           hat
alice         glasses
tim           shoes

我会这样做(实际上没有尝试过，只是想法):

I would do this like this (not actually tried, just the idea):

SELECT customer.name as customer_name,
  MODE(item_bought) as item_bought_most_ofen
FROM customers
INNER JOIN purchases USING (customer_id)
GROUP_BY customer_id

但是，MODE 聚合函数在 Redshift 中不存在.

However, the MODE aggregation function doesn't exist in Redshift.

似乎 Redshift 用户定义函数 只是常规标量函数，而不是聚合函数.所以我不认为我可以自己定义它.

It seems that Redshift user defined functions are just regular scalar functions, not aggregations functions. So I don't think I can define it myself.

有什么解决方法吗?

推荐答案

您可以通过使用 row_number() 来模拟 mode():

You can mimic mode() by using row_number():

select name, item_bought
from (select c.name, p.item_bought, count(*) as cnt,
             row_number() over (partition by c.name order by count(*) desc) as seqnum
      from customers c join
           purchases p
           using (customer_id)
      group by c.name, p.item_bought
     ) cp
where seqnum = 1;

这篇关于带红移的 MODE 聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！