我需要以下数据:例如瑞典男性品牌A EUR瑞典女性品牌A EUR瑞典男性品牌B EUR英国男性品牌A EUR英国女性品牌A EUR英国男性品牌B EUR英国女性品牌C EUR
欧元来自8000个人,5000使用欧元,2000使用gbp和1000使用SEk的事实。因此,最受欢迎的货币是欧元。我希望每行都显示它
获得最受欢迎的货币max_curr
对于返回的每一行,都有一个名为max_curr的列,并显示此值
select
cdl.country
,cd.gender
,cd.brand
,t.max_curr
from customer_data_list cdl
left join customer_data cd on cd.customerid = cdr.customerid
left join
(
select
trans.customerid, a.*
from
(
select
a.*
,max(count_currency) over() as max_curr
FROM
(
select
t.currency
,count(t.currency) as count_currency
from transactions t
where t.function = 'DEPOSIT' and t.status = 'ACCEPTED'
group by t.currency
order by currency
) a
) a
left join transactions trans on trans.currency = a.currency
where count_currency = max_curr) t on t.customerid = cdl.customerid
这没有返回我的要求,因为大多数max_curr列条目为空。有人可以帮忙吗?
如果对于每个客户,您想要最常用于接受,存款交易的货币,那么可以使用聚合和窗口函数:
select cc.*
from (select t.customerid, t.currency, count(*) as cnt,
row_number() over (partition by t.customerid order by count(*) desc) as seqnum
from transactions t
where t.function = 'DEPOSIT' and
t.status = 'ACCEPTED'
group by t.customerid, t.currency
) cc
where seqnum = 1;
编辑:
对于问题的修订解释,只需使用交叉联接:
select t.customerid, t.currency, count(*) as cnt,
tt.currency as most_popular_currency
from transactions t cross join
(select t.currency
from transactions t
where t.function = 'DEPOSIT' and
t.status = 'ACCEPTED'
group by t.currency
order by count(*) desc
limit 1
) tt
where t.function = 'DEPOSIT' and
t.status = 'ACCEPTED'
group by t.customerid, t.currency
感谢您的回覆。我希望为所有客户显示总体最受欢迎的货币。因此,换句话说,所有列都将相同。如果总体上欧元是最受欢迎的,那么所有客户的max_curr列将为EUR