SQL - Запрос. Для каждого читателя посчитать, сколько раз он первым брал любую книгу

Question

SQL - Запрос. Для каждого читателя посчитать, сколько раз он первым брал любую книгу

Рейтинг: 1Ответов: 2Опубликовано: 11.08.2023

Помогите, пожалуйста, решить задачу: Для каждого читателя посчитать, сколько раз он первым брал любую книгу. Если гость не брал книг - вывести 0. Вывести фамилию читателя и посчитанное значение. Последний столбец назвать Количество. Информацию отсортировать сначала по убыванию количества, а затем по фамилии читателя в алфавитном порядке.

На этапе соединения запросов в табличное выражение я поплыл и никак не могу понять как правильно написать на MySQL. Это мое не рабочее табличное выражение:

WITH GET_DATES1(book_id, MIN_borrow_date) AS (
    SELECT book_id
         , MIN(borrow_date)
    FROM book_reader   
    GROUP BY 1
),
GET_DATES2(reader_id) AS (
    SELECT reader_id
    FROM book_reader
    INNER JOIN GET_DATES1 USING(book_id) 
        AND book_reader.borrow_date = GET_DATES1.MIN_borrow_date
) 
SELECT reader_name
     , COUNT(borrow_date) AS Количество   
FROM reader  
LEFT JOIN GET_DATES2 USING(reader_id) 
GROUP BY 1;

sql mysql

Источник: Stack Overflow на русском

Answer 1

▲ 1

Поправил вот так, но результат не верный :) блин

WITH GET_DATES1(reader_name, book_id, Количество) 
AS (SELECT reader_name, book_id, MIN(borrow_date) AS Количество
FROM book_reader
RIGHT JOIN reader ON book_reader.reader_id = reader.reader_id
GROUP BY reader_name, book_id)
SELECT reader_name, COUNT(Количество) AS Количество   
FROM GET_DATES1
GROUP BY reader_name
ORDER BY Количество DESC, reader_name;

Answer 2

▲ 1

Посмотрите пример

-- test data
create table reader(reader_id int,reader_name varchar(10));
insert into reader values
 (1001,'Name1001')
,(1002,'Name1002')
,(1003,'Name100x')
,(1004,'Name100x')
;
create table book_reader(book_reader_id int, book_id int,reader_id int
  ,borrow_date date,return_date date);
insert into book_reader values
 (1,101,1001,'2023-01-01','2023-01-05')
,(2,101,1002,'2023-01-06','2023-01-12')
,(3,102,1003,'2023-01-06','2023-01-12')
;
-- query
with rnk as(
  select *
       -- rn=1 - первый раз взяли книгу эту книгу (для каждого book_id)
    ,row_number()over(partition by book_id order by borrow_date)rn
  from book_reader
 )
,Book1Cnt as(
select reader_id
     -- если rn=1  - этот читатель первым взял книгу (точнее - раньше всех)
  ,sum(case when rn=1 then 1 else 0 end) cnt
 from rnk
 group by reader_id
 )
 select reader.reader_id,reader_name,coalesce(cnt,0) cnt
 from reader -- нужны все читатели, бравшие или не бравшие книги
 left join Book1Cnt on Book1Cnt.reader_id=reader.reader_id
 ;

Результат на тестовых данных

reader_id	reader_name	cnt
1001	Name1001	1
1002	Name1002	0
1003	Name100x	1
1004	Name100x	0

Пример

Ваш первоначальный вариант должен выглядеть примерно так

WITH GET_DATES1(book_id, MIN_borrow_date) AS (
    SELECT book_id,MIN(borrow_date) MIN_borrow_date
    FROM book_reader   
    GROUP BY book_id
),
GET_DATES2(reader_id) AS (
    SELECT reader_id
    FROM book_reader
    INNER JOIN GET_DATES1 on book_reader.book_id=GET_DATES1.book_id
        AND book_reader.borrow_date = GET_DATES1.MIN_borrow_date
) 
SELECT min(reader_name) as reader_name
     , COUNT(GET_DATES2.reader_id) AS Количество   
FROM reader  
LEFT JOIN GET_DATES2 on GET_DATES2.reader_id=reader.reader_id
GROUP BY reader.reader_id;

Повторю вслед за @teran, применение алиасов и правильным наименованием промежуточных и вспомогательных объектов полезно.
Назовите, GET_DATES - например First_book_borrow_dates и алиас fbbd или другое понятное Вам и другим читателям наименование.

SQL - Запрос. Для каждого читателя посчитать, сколько раз он первым брал любую книгу

Ответы