0

I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is returned. I need the SELECT statement to calculate a column that means "This is the customer's Nth purchase for the day." The data is sorted by customer name and date already, so when the customer name or date changes, I want the counter variable to reset to 1.

 ----------------------------------------------------------------
| Customer Name | Product | PurchaseDate | PurchaseNumberForDate |
 ----------------------------------------------------------------
| Customer A    | ...     | 2019-04-01   | 1                     |
| Customer A    | ...     | 2019-04-02   | 1                     |
| Customer A    | ...     | 2019-04-03   | 1                     |
| Customer A    | ...     | 2019-04-03   | 2                     |
| Customer A    | ...     | 2019-04-03   | 3                     |
| Customer B    | ...     | 2019-04-03   | 1                     |
| Customer B    | ...     | 2019-04-03   | 2                     |
| Customer B    | ...     | 2019-04-04   | 1                     |
 ----------------------------------------------------------------

I have tried using MySQL variables, but I cannot figure out how to reset the counter conditionally when the customer name or purchase date change. If I could get the PurchaseNumberForDate correctly calculated, I will use this as a subquery with another query that will select WHERE PurchaseNumberForDate = 1.

I have found plenty of examples using COUNT() and @var := @var+1, but I haven't found one based on multiple conditions. Is this possible with MySQL?

  • Try a correlated subquery counting the purchases a customer made before. – sticky bit Apr 3 '19 at 21:04
  • That works, but it is much slower. The purchases table has over 1 million records and the correlated subquery is evaluating them again for every result I got from the first pass. For narrow date ranges it is acceptable, but larger ranges are not completing after 3+ minutes. Without the subquery, the longest runtime is around 5 seconds. Maybe a MySQL procedure could process the subquery and produce a temporary table with the extra column derived by logic? – jimp Apr 3 '19 at 21:49
  • Wouldn't that also need to calculate each value? But if you only need the first purchase and not an arbitrary one, maybe you can try group by the date taking the minimum of whatever orders the purchases and join that back. Or update to versions of the DBMS, that allow window functions, that would make it rather easy. – sticky bit Apr 3 '19 at 21:56
  • And/or think about indexes that can help. – sticky bit Apr 3 '19 at 22:02
  • The calculation would be minimal when done across the result set. I already have all the data I am interested in. I just need an extra column that is derived from some logic. It looks like procedures and cursors available since v5.5 should get the job done. dev.mysql.com/doc/refman/5.5/en/cursors.html I should be able to filter my current results into a temporary table with the extra column derived. – jimp Apr 3 '19 at 22:17
0
SELECT t.*, 
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END PurchaseNumberForDate,
@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) groupname
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate 

Variant without additional field:

SELECT t.*, 
CASE WHEN CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate) = @group
     THEN @num:=@num+1
     ELSE @num:=1
     END + CONCAT(CHAR(0), (@group:=CONCAT(t.CustomerName, CHAR(0), t.PurchaseDate))) PurchaseNumberForDate
FROM datatable t, (SELECT @group:='', @num:=0) vars
ORDER BY t.CustomerName, t.PurchaseDate;

fiddle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.