Блог

Главная - Вопросы по программированию - Я хочу запросить отсутствие транзакции, совершенной клиентом в определенном году, но результат должен быть получен в годовом исчислении для каждого клиента в формате таблицы

Я хочу запросить отсутствие транзакции, совершенной клиентом в определенном году, но результат должен быть получен в годовом исчислении для каждого клиента в формате таблицы

#mysql #sql

Вопрос:

Вывод должен быть в формате ниже, но я получаю неверный вывод: где столбец 2019,2020,2021 содержит транзакцию, выполненную клиентом соответственно в 2019, 2020, 2021 годах. Также, если транзакции в 2019,2020,2021 равны, Max_transaction заполняется первым ненулевым годом транзакции .

 customer_name    2019   2020 2021  Max_transaction_year  total_transaction
pug                2    1      0        2019                 4
hari               0    1      1        2020                 2
adh                0    0      1        2021                 1

Пример таблицы и данных :

Также обратите внимание, что первые две цифры в «tid» представляют год транзакции. Например: 19597 -«19» означает 2019 год и так далее для 2020 и 2021 годов.

 create table client (cid int,cname char(10));
create table trans (tid int,cid int);
insert into client values(102,'pug'),(107,'ravi'),(109,'hari'),(105,'pon'),(106,'adh'),(104,'bav'),(101,'kat');
insert into trans values(19597,102),(19567,102),(20325,109),(21789,106),(17432,106),(21786,109),(20302,102),(17301,103);


Thanks in advance

Ответ №1:

Схема (MySQL v8.0)

 create table client (cid int,cname char(10));
create table trans (tid int,cid int);
insert into client values(102,'pug'),(107,'ravi'),(109,'hari'),(105,'pon'),(106,'adh'),(104,'bav'),(101,'kat');
insert into trans values(19597,102),(19567,102),(20325,109),(21789,106),(17432,106),(21786,109),(20302,102),(17301,103);

Запрос № 1

 SELECT 
    customer_name,
    SUM(
        CASE WHEN year=2019 THEN no_transactions ELSE 0 END
    ) as '2019',
    SUM(
        CASE WHEN year=2020 THEN no_transactions ELSE 0 END
    ) as '2020',
    SUM(
        CASE WHEN year=2021 THEN no_transactions ELSE 0 END
    ) as '2021',
    MAX(
        CASE WHEN rn=1 THEN year ELSE 0 END
    ) as Max_transaction_year,
    SUM(no_transactions) as total_transaction
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_name ORDER BY no_transactions DESC) rn
    FROM (
        SELECT
            c.cname as customer_name, 
            2000 FLOOR(tid / 1000) as year , 
            COUNT(1) as no_transactions
        FROM
            trans t
        INNER JOIN
            client c ON t.cid = c.cid
        WHERE 
            FLOOR(tid / 1000) BETWEEN 19 and 21
        GROUP BY
            c.cname, 2000 FLOOR(tid / 1000) 
    ) p1
) p2
GROUP BY customer_name;
имя пользователя 2019 2020 2021 Max_transaction_year total_transaction
адг 0 0 1 2021 1
хари 0 1 1 2020 2
мопс 2 1 0 2019 3

Просмотр на скрипке БД

Комментарии:

1. Когда я запускаю этот запрос, я получаю вывод, но порядок столбцов другой, Как мы получаем имя пользователя в качестве 1-го столбца : 2019 2020 2021 имя пользователя Max_transaction_year total_transaction

2. @rahulkushwaha Какое программное обеспечение вы используете для выполнения запроса?

3. @rahulkushwaha Я попробовал это с помощью mysql 5.5 —> 8, и порядок столбцов одинаковый, используя ссылку на скрипку бд, указанную в ответе

4. спасибо за эту ссылку, я ее пропустил. Однако в столбце «Max_transaction_year» я ожидал год, за который клиент совершил максимальное количество транзакций. Например, в выводе для «мопса» ясно, что это 2019 год, когда он совершил максимальное количество транзакций. Заранее спасибо.

5. У клиента @ pug rahulkushwaha есть идентификатор 102 и 3 транзакции, основанные на общих данных образца (19597,102),(19567,102),(20302,102) . Последняя транзакция с идентификатором 20302 подразумевает, что pug максимальная транзакция была совершена в 2020 году, если первые 2 цифры представляют год. Правильно ли это?

Ответ №2:

Я думаю, что несколько более простой метод просто использует условную агрегацию:

 select cname, cnt_2019, cnt_2020, cnt_2021,
       (case greatest(cnt_2019, cnt_2020, cnt_2021)
            when cnt_2019 then '2019'
            when cnt_2020 then '2020'
            when cnt_2021 then '2021'
        end) as max_year,
       total_transactions
from (select c.cname, c.cid,
             sum(year = '2019') as cnt_2019,
             sum(year = '2020') as cnt_2020,
             sum(year = '2021') as cnt_2021,
             count(*) as total_transactions
      from client c join
           (select t.*, concat('20', left(t.tid, 2)) as year
            from trans t
           ) t
           on c.cid = t.cid
      where year >= '2019' and year <= '2021'
      group by c.cname, c.cid
     ) ct
order by cname, cid;

Вот скрипка db<>.

Комментарии:

1. Спасибо @Gordon и за это решение.

Метки: , ,