如何根据特定的复杂规则对行进行计数

Question

我有下表：

custid  custname  channelid  channel  dateViewed
--------------------------------------------------------------
1          A       1         ABSS     2016-01-09  
2          B       2         STHHG    2016-01-19 
3          C       4         XGGTS    2016-01-09 
6          D       4         XGGTS    2016-01-09 
2          B       2         STHHG    2016-01-26 
2          B       2         STHHG    2016-01-28 
1          A       3         SSJ      2016-01-28 
1          A       1         ABSS     2016-01-28 
2          B       2         STHHG    2016-02-02
2          B       7         UUJKS    2016-02-10
2          B       8         AKKDC    2016-02-10
2          B       9         GGSK     2016-02-10
2          B       9         GGSK     2016-02-11
2          B       7         UUJKS    2016-02-27

我希望结果是：

custid  custname  month count  
------------------------------
1          A       1     1
2          B       1     1      
2          B       2     4     
3          C       1     1
6          D       1     1

根据以下规则：

• 所有频道观看订阅每15天收费一次。如果客户在15天内观看了同一频道，他只会该频道收费一次。例如，custid 2，custname B他的计费周期是1月19日-2月3日（一个计费周期），2月4日-2月20日（一次计费周期）等等。因此，他在1月只被计费1次，因为他在整个计费周期中都在看同一个频道；他在2月因观看（频道ID 7、8、9）和2月27日观看频道ID 7而被计费4次（由于这属于另一个计费周期，客户B也在这里收费）。客户B在2月2日观看第2频道时不收取费用，因为他已经在1月19日至2月3日的计费周期内收到了账单
• 每个客户每月都会生成一张发票，因此结果应显示通道的"月份"one_answers"计数"为每个客户查看

这可以在SQL server中完成吗？

Answer 1

;WITH cte AS (
    SELECT  custid,
            custname, 
            channelid, 
            channel, 
            dateViewed,
            CAST(DATEADD(day,15,dateViewed) as date) as dateEnd,
            ROW_NUMBER() OVER (PARTITION BY custid, channelid ORDER BY dateViewed) AS rn
    FROM (VALUES
        (1, 'A', 1, 'ABSS', '2016-01-09'),(2, 'B', 2, 'STHHG', '2016-01-19'),
        (3, 'C', 4, 'XGGTS', '2016-01-09'),(6, 'D', 4, 'XGGTS', '2016-01-09'),
        (2, 'B', 2, 'STHHG', '2016-01-26'),(2, 'B', 2, 'STHHG', '2016-01-28'),
        (1, 'A', 3, 'SSJ', '2016-01-28'),(1, 'A', 1, 'ABSS', '2016-01-28'),
        (2, 'B', 2, 'STHHG', '2016-02-02'),(2, 'B', 7, 'UUJKS', '2016-02-10'),
        (2, 'B', 8, 'AKKDC', '2016-02-10'),(2, 'B', 9, 'GGSK', '2016-02-10'),
        (2, 'B', 9, 'GGSK', '2016-02-11'),(2, 'B', 7, 'UUJKS', '2016-02-27')
        ) as t(custid, custname, channelid, channel, dateViewed)
), res AS (
    SELECT custid, channelid, dateViewed, dateEnd, 1 as Lev
    FROM cte
    WHERE rn = 1
    UNION ALL
    SELECT c.custid, c.channelid, c.dateViewed, c.dateEnd, lev + 1
    FROM res r
    INNER JOIN cte c ON c.dateViewed > r.dateEnd and c.custid = r.custid and c.channelid = r.channelid
), final AS (
    SELECT  * ,
            ROW_NUMBER() OVER (PARTITION BY custid, channelid, lev ORDER BY dateViewed) rn,
            DENSE_RANK() OVER (ORDER BY custid, channelid, dateEnd) dr
    FROM res
)
SELECT  b.custid,
        b.custname, 
        MONTH(f.dateViewed) as [month],
        COUNT(distinct dr) as [count]
FROM cte b
LEFT JOIN final f
    ON b.channelid = f.channelid and b.custid = f.custid and b.dateViewed between f.dateViewed and f.dateEnd
WHERE f.rn = 1
GROUP BY b.custid,
        b.custname, 
        MONTH(f.dateViewed)

输出：

custid      custname month       count
----------- -------- ----------- -----------
1           A        1           3
2           B        1           1
2           B        2           4
3           C        1           1
6           D        1           1
(5 row(s) affected)

我不知道为什么您在客户A的count字段中得到1。他得到了：

ABSS     2016-01-09 +1 to count (+15 days = 2016-01-24)
SSJ      2016-01-28 +1 to count
ABSS     2016-01-28 +1 to count (28-01 > 24.01)

所以一月份一定会有count = 3。

Answer 2

每当我试图用复杂的标准来计算事物时，我都会使用sum-and-case语句。如下所示：

SELECT custid, custname, 
    SUM(CASE WHEN somecriteria
        THEN 1
        ELSE 0
        END) As CriteriaCount
FROM whateverTable
GROUP BY custid, custname

您可以使somecriteria变量成为一个复杂的语句，只要它返回布尔值即可。如果通过，此行将返回1。如果失败，行会重新使用0，然后我们将返回的值相加以获得计数。

Answer 3

一般来说，这就是从给定日期（本例中为@dd）开始获得任意数字（本例为10）的固定15天间隔的方法。

DECLARE @dd date = CAST('2016-01-19 17:30' AS DATE);
WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), 
E4(N) AS (SELECT 1 FROM E2 a, E2 b),  --10,000 rows max
tally(N) AS (SELECT TOP (10) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
SELECT
    startd = DATEADD(D,(N-1)*15, @dd),
    endd = DATEADD(D, N*15-1, @dd)    
FROM tally

使其适应定义用户（可能还有chanel）必须如何计算开始日期的规则。

Answer 4

@Sturgus如果我想在代码中定义它呢？任何其他除了在表格中定义它之外的其他选择？如何编写查询可以每月运行以生成每月发票。–星期六15分钟前

好吧，无论如何，你必须保存每个客户的账单开始日期（最低限度）。如果您想在不"编辑数据库"的情况下完全在SQL中完成此操作，那么以下操作应该有效。这种方法的缺点是，您需要每月手动编辑"INSERTINTO"语句以满足您的需要。如果允许您编辑现有的客户表或创建一个新的客户表，则可以减少手动操作。

DECLARE @CustomerBillingPeriodsTVP AS Table(
   custID int UNIQUE,
   BillingCycleID int,
   BillingStartDate Date,
   BillingEndDate Date
);
INSERT INTO @CustomerBillingPeriodsTVP (custID, BillingCycleID, BillingStartDate, BillingEndDate) VALUES
 (1, 1, '2016-01-03', '2016-01-18'), (2, 1, '2016-01-18', '2016-02-03'), (3, 1, '2016-01-15', '2016-01-30'), (6, 1, '2016-01-14', '2016-01-29');
SELECT A.custid, A.custname, B.BillingCycleID AS [month], COUNT(DISTINCT A.channelid) AS [count]
FROM dbo.tblCustomerChannelViews AS A INNER JOIN @CustomerBillingPeriodsTVP AS B ON A.custid = B.CustID
GROUP BY A.custid, A.custname, B.BillingCycleID;
GO

您从哪里获得客户的账单开始日期？

Answer 5

我不确定这个解决方案将如何扩展，但有了一些好的候选索引和良好的数据管理，它就会工作。。

你将需要一些额外的信息，为初学者，并规范化你的数据。您需要知道每个客户的第一个充电期开始日期。所以把它放在顾客桌上。

以下是我使用的表格：

create table #channelViews
(
    custId int, channelId int, viewDate datetime
)
create table #channel 
(
    channelId int, channelName varchar(max)
)
create table #customer 
(
    custId int, custname varchar(max), chargingStartDate datetime
)

我将填充一些数据。我不会得到与您的样本输出相同的结果，因为我没有为每个客户提供合适的开始日期。客户2可以。

insert into #channel (channelId, channelName)
select 1, 'ABSS'
union select 2, 'STHHG'
union select 4, 'XGGTS'
union select 3, 'SSJ'
union select 7, 'UUJKS'
union select 8, 'AKKDC'
union select 9, 'GGSK'
insert into #customer (custId, custname, chargingStartDate)
select 1, 'A', '4 Jan 2016'
union select 2, 'B', '19 Jan 2016'
union select 3, 'C', '5 Jan 2016'
union select 6, 'D', '5 Jan 2016'
insert into #channelViews (custId, channelId, viewDate)
select 1,1,'2016-01-09'  
union select 2,2,'2016-01-19' 
union select 3,4,'2016-01-09' 
union select 6,4,'2016-01-09' 
union select 2,2,'2016-01-26' 
union select 2,2,'2016-01-28' 
union select 1,3,'2016-01-28' 
union select 1,1,'2016-01-28' 
union select 2,2,'2016-02-02'
union select 2,7,'2016-02-10'
union select 2,8,'2016-02-10'
union select 2,9,'2016-02-10'
union select 2,9,'2016-02-11'
union select 2,7,'2016-02-27'

这是一个有点不奇怪的查询，在一个语句中。两个底层子查询实际上是相同的数据，因此可能有更合适/更有效的方法来生成这些数据。

我们需要将上个月在同一收费期C内收费的任何频道排除在计费之外。这就是联接的本质。我使用了右联接，这样我就可以从结果中排除所有这样的匹配（使用old.custId is null）。

select c.custId, c.[custname], [month], count(*) [count] from 
(
    select new.custId, new.channelId, new.month, new.chargingPeriod
    from 
        (
        select distinct cv.custId, cv.channelId, month(viewdate) [month], (convert(int, cv.viewDate) - convert(int, c.chargingStartDate))/15 chargingPeriod
        from #channelViews cv join #customer c on cv.custId = c.custId
        ) old
    right join 
        (
        select distinct cv.custId, cv.channelId, month(viewdate) [month], (convert(int, cv.viewDate) - convert(int, c.chargingStartDate))/15 chargingPeriod
        from #channelViews cv join #customer c on cv.custId = c.custId
        )  new
    on old.custId = new.custId 
        and old.channelId = new.channelId 
        and old.month = new.Month -1
        and old.chargingPeriod = new.chargingPeriod
    where old.custId is null
    group by new.custId, new.month, new.chargingPeriod, new.channelId
) filteredResults
join #customer c on c.custId = filteredResults.custId
group by c.custId, [month], c.custname
order by c.custId, [month], c.custname

最后是我的结果：

custId custname month count
1      A        1     3
2      B        1     1
2      B        2     4
3      C        1     1
6      D        1     1

这个查询做同样的事情：

select c.custId, c.custname, [month], count(*) from 
(
    select cv.custId, min(month(viewdate)) [month], cv.channelId
    from #channelViews cv join #customer c on cv.custId = c.custId
    group by cv.custId, cv.channelId, (convert(int, cv.viewDate) - convert(int, c.chargingStartDate))/15
) x
join #customer c 
 on c.custId = x.custId
group by c.custId, c.custname, x.[month]
order by custId, [month]