如何确定客户历史记录的第一条记录中的单元格是否包含值？

I have a Tickets table that holds several years with of customer data. I am trying to determine whether a customer who has visited x number of time used a coupon on their first visit. I have this (SQL below) which I am using so that I can break out the group I am looking for. What I really want to know about this group is how many of them used a coupon on their first visit (earliest SaleDate).

我有一张Tickets表,可以存储几年的客户数据。我正在尝试确定访问了x次的客户是否在首次访问时使用了优惠券。我有这个(下面的SQL),我正在使用,以便我可以打破我正在寻找的组。我真正想知道的是这个群体中有多少人在第一次访问时使用了优惠券(最早的SaleDate)。

;WITH T AS
(
SELECT CustomerId, SaleDate, TotalCoupons,
       COUNT(*) OVER (PARTITION BY CustomerID) as Cnt
FROM    Store.dbo.Tickets
Where   (CreationDate between '2014-1-1' and '2014-11-12')
        and (IsDeleted = 0)
        And (TicketStatus = 'Complete')
)
SELECT CustomerID, SaleDate, TotalCoupons, Cnt                   
FROM T
WHERE Cnt = 10
order by CustomerId, SaleDate

As an end result I just need a count and don't need to see the CustomerID, SaleDate etc, I just need a (pseudo) Select Count(CustomerID) where TotalCoupons cell has a value on it and it is the earliest record for that CustomerID.

作为最终结果,我只需要一个计数而不需要查看CustomerID,SaleDate等,我只需要一个(伪)选择计数(CustomerID),其中TotalCoupons单元格上有一个值,它是最早的记录客户ID。

I think I have to add a RowID and use a Where RowID = 1 but I am not sure how to work it into what I have done.

我想我必须添加一个RowID并使用Where RowID = 1,但我不知道如何将其用于我所做的事情。

1 个解决方案

#1

You can use row_number() to determine the first visit and then use conditional aggregation. The following gets the customers in the group with the flag:

您可以使用row_number()来确定第一次访问,然后使用条件聚合。以下内容使用标志获取组中的客户:

WITH T AS (
      SELECT CustomerId, SaleDate, TotalCoupons,
             COUNT(*) OVER (PARTITION BY CustomerID) as Cnt,
             ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY SaleDate) as seqnum
      FROM Store.dbo.Tickets
      Where (CreationDate between '2014-01-01' and '2014-11-12') and
            (IsDeleted = 0) and
            (TicketStatus = 'Complete')
     )
SELECT CustomerID, SaleDate, TotalCoupons, Cnt,
       MAX(CASE WHEN seqnum = 1 and TotalCoupons IS NOT NULL) as HasCouponFirstVisit              
FROM T
WHERE Cnt = 10;

You can get a count of them by doing:

您可以通过执行以下操作来计算它们:

WITH T AS (
      SELECT CustomerId, SaleDate, TotalCoupons,
             COUNT(*) OVER (PARTITION BY CustomerID) as Cnt,
             ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY SaleDate) as seqnum
      FROM Store.dbo.Tickets
      Where (CreationDate between '2014-01-01' and '2014-11-12') and
            (IsDeleted = 0) and
            (TicketStatus = 'Complete')
     )
SELECT COUNT(CustomerID)
FROM T
WHERE Cnt = 10 AND seqnum = 1 AND TotalCoupons IS NOT NULL;

I'm not sure if the TotalCoupons IS NOT NULL is exactly what you are looking for, but it should be close enough.

我不确定TotalCoupons IS NOT NULL是否正是您正在寻找的,但它应该足够接近。

#1