Блог

Главная - Вопросы по программированию - Отчет о первой и последней транзакции SQL

Отчет о первой и последней транзакции SQL

#sql-server

#sql-сервер

Вопрос:

У меня есть таблица в SQL, которая выглядит немного похожей на таблицу ниже:

[введите описание изображения здесь]1

Моя цель — создать отчет о первой и последней транзакции. Я хочу знать, когда клиент X совершил свою первую покупку и какова дата их последней покупки. Я хотел бы сгруппировать свои результаты по хранилищу и добавить все транзакции, если они происходят в один и тот же день.

Например, если Джон до совершил 2 расходы в walmart 15 января, и это их самая последняя транзакция, я бы хотел, чтобы эти две расходы были суммированы в моем отчете.

Вот конечный результат, который я ожидаю от таблицы, подобной приведенному выше примеру:

[введите описание изображения здесь]2

С тем, что я пробовал до сих пор, я возвращаю только 1 значение

SQL выглядит немного похоже на

 Select 
SN
, SID
, CustomerName
, BankAccount
, Min(TransDate)
, Max(TransDate)
, price
, store
From transaction
GROUp by 
SN
, SID
, CustomerName
, BankAccount
, Min(TransDate)
, Max(TransDate)
, price
, store

Я знаю, что для получения результата (возможно) мне нужно использовать некоторые типы вложенных запросов, но мне это не удалось.

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

1. Неясно, что вы хотите показать в цене, я вижу 10 для Питера, но у этого пользователя два 10 и 12, как вы решили, что он должен показывать 10 и 20 для других?

2. Разбейте свою цель на части, которые вы можете выполнить. Первый шаг — для каждого кортежа <customer, store> найдите две (или, возможно, одну) даты, которые вам нужны. Обратите внимание, что не следует предполагать, что самая ранняя (минимальная) дата отличается от самой последней (максимальной) даты. Получив этот набор строк, вы можете присоединиться к своей таблице, чтобы получить нужные транзакции. Очевидно, вы собираетесь суммировать транзакции — так что ПОДУМАЙТЕ о том, что значит включать столбец, специфичный для строки, а не агрегированный. Подсказка — не делайте ваши образцы данных слишком упрощенными.

3. конечная цель — получить первую дату первой транзакции данного клиента, а также получить последнюю транзакцию этого клиента… если у данного клиента более одной транзакции на последнюю дату, я хочу суммировать суммы, если у него только одна транзакция, я верну эту единственную транзакцию в отчет, .. @ rs. имеет ли это больше смысла?

Ответ №1:

Я бы попытался извлечь информацию о клиенте, необходимую для фильтрации остальных данных. Я бы начал с CTE, чтобы получить минимальные и максимальные даты для каждого клиента. Затем я бы ПРИСОЕДИНИЛ эту резервную копию к исходной таблице и просто получил записи для клиента, которые относятся к этой МАКСИМАЛЬНОЙ дате.

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

 CREATE TABLE #tmp(SerialNumber int, SearialID int,CustomerName varchar(100), BankAccount int, 
                    TranDate date, Price decimal(10,2),TracerID int,Store varchar(20))

INSERT INTO #tmp VALUES
(2,2,'Peter Smith',14564,'1/1/2021',10,756,'Kroger'),
(1,1,'John Do',12345,'1/1/2021',10,156,'Walmart'),
(1,1,'John Do',12345,'1/15/2021',5,148,'Walmart'),
(1,1,'John Do',12345,'1/15/2021',15,148,'Walmart'),
(2,2,'Peter Smith',14564,'1/12/2021',12,756,'Kroger')

;WITH CTE AS
    (
    SELECT Min(TranDate) FirstDate, Max(TranDate) LastDate, CustomerName
    FROM #tmp
    GROUP BY CustomerName
    )
SELECT T.SerialNumber,T.SearialID,T.CustomerName,t.BankAccount,C.FirstDate FirstTrans, C.LastDate,SUM(t.Price) Price,T.Store 
    FROM #tmp T
    INNER JOIN CTE C on t.CustomerName = c.CustomerName and t.TranDate = c.LastDate
GROUP BY T.SerialNumber,T.SearialID,T.CustomerName,t.BankAccount,C.FirstDate,C.LastDate,T.Store

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

1. Итак, мое решение выглядело немного похоже на это. Я использовал внутренний запрос без временной таблицы… Но это можно сделать и с помощью временной таблицы Выберите T1.SerialNumber, T1.SerialID, T1.CustomerName, T2.FirstTrans, T2.LastTrans ИЗ transstable T1 Inner JOIN — исходная таблица) (выберите SerialNumber, SerialID, Min(FirstTrans) ‘FirstTrans’, Max(LastTrans) ‘LastTrans’ Из ГРУППЫ TRANSSTABLE ПО SerialNumber, SeriealID ) T2 НА T2.LastTrans = T1.TransDate И T2.SerialNumber = T1.SerialNumber И T2.SerialID = T1.SerialID

Метки: