Warm tip: This article is reproduced from stackoverflow.com, please click
sql amazon-redshift

Most popular category listed with every row redshift database

发布于 2020-03-28 23:17:33

I require the following data: e.g. sweden male brandA EUR sweden female brandA EUR sweden male brandB EUR england male brandA EUR england female brandA EUR england male brandB EUR england female brandC EUR

EUR came from the fact that of 8000 people, 5000 use euro, 2000 use gbp and 1000 use SEk . SO the most popular currency is EURO. I would like it displayed for every line

to get the most popular currency, max_curr

and for every row returned to have a column called max_curr with this value displayed

    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

This is not returning what I require because most max_curr column entries are empty. Can someone help please?

Questioner
Lilz
Viewed
17
分享
Gordon Linoff 2020-02-01 01:12

If, for each customer, you want the currency that is used most often for ACCEPTED, DEPOSIT transactions, then you can use aggregation and window functions:

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;

EDIT:

For the revised interpretation of the question, just use a cross join:

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
Lilz 2020-01-31 18:54:54

Thank you for replying. I would like the overall most popular currency to be displayed for all customers . So in other words, the column would be the same for all. If EUR is the most popular overall, then the column max_curr would be EUR for all customers

Related issues

1
Query is possibly slow?
2
Simple Sql Role query issue
3
Group by query with rollup
4
How to include a PHP variable inside a MySQL statement
5
Getting empty rows from "SELECT ALL" query in PostgreSQL
6
MySQL pivot table using java
7
Implement smart search by text field in database
8
How to speed up count(distinct) with Between clause in MySQL
9
how to perform sql query MIN/MAX FILTER
10
Dynamic pivot in oracle sql