用于 Microsoft Access 2013 的 SQL

Question

我正在尝试计算返回以获得额外服务的客户的数量，该数量考虑了服务之前的评估分数比较，按服务类型分组。 (最终，我还希望能够忽略原始服务后一个月内的退货，但我很确定我可以自己解决这个问题)

在计算特定服务的结果时，它应该查看任何服务类型的返回，而不仅仅是原始服务类型。 (编辑:*它还应该查看所有 future 的返回，而不仅仅是下一个或最近的*)。

它不需要经常运行，但是有 15000+ 行数据，并且计算资源受到动力不足的机器的限制(这是针对非营利组织的)，因此效率会很好，但不是绝对需要的。

样本数据

ServiceTable

CustomerID  Service  Date   ScoreBefore

   A    Service1    1/1/2017    1
   A    Service2    1/3/2017    1
   A    Service1    1/1/2018    4
   B    Service3    3/1/2018    3
   B    Service1    6/1/2018    1
   B    Service1    6/2/2018    1
   C    Service2    1/1/2019    4
   C    Service2    6/1/2019    1

结果应该是(不考虑日期填充选项):

Service1
  ReturnedWorse 0
  ReturnedSame  2
  ReturnedBetter 1

Service2
  ReturnedWorse 1
  ReturnedSame 0
  ReturnedBetter 1

Service3
  ReturnedWorse 2

到目前为止，我已经尝试创建生成表查询，然后可以查询这些查询以获取聚合信息，但我有点卡住并怀疑可能有更好的路线。

我试过的:

SELECT CustomerID, Service, Date, ScoreBefore INTO ReturnedWorse
FROM ServiceTable AS FirstStay
WHERE ((((SELECT COUNT(*)
FROM ServiceTable AS SecondStay
WHERE FirstStay.CustomerID=SecondStay.CustomerID
AND
FirstStay.ScoreBefore> SecondStay.ScoreBefore
AND
SecondStay.Date > FirstStay.Date))));

任何帮助将不胜感激。

Answer 1

使用窗口函数会更容易做到这一点，但它们在 ms-access 中不可用。

这是一个查询，可以解决我对您的问题的理解:

t0 :在表中选择一条记录(客户购买服务)

t1 : 提取下一次同一客户与INNER JOIN 签订任何服务时对应的记录和相关子查询(如果没有这样的记录，则不考虑初始记录)

比较上一条记录的分数和当前记录的分数

按服务 ID 对结果进行分组

您可以在 this db fiddlde 中看到它的实际效果。 .结果与您的预期略有不同(请参阅我的评论)......但它们与上述解释一致；您可能希望使用相同的原则调整某些规则以匹配您的确切预期结果。

SELECT
    t0.service,
    SUM(CASE WHEN t1.scorebefore < t0.scorebefore THEN 1 ELSE 0 END) AS ReturnedWorse,
    SUM(CASE WHEN t1.scorebefore = t0.scorebefore THEN 1 ELSE 0 END) AS ReturnedSame,
    SUM(CASE WHEN t1.scorebefore > t0.scorebefore THEN 1 ELSE 0 END) AS ReturnedBetter
FROM 
    mytable t0
    INNER JOIN mytable t1
        ON  t0.customerid = t1.customerid
        AND t0.date < t1.date
        AND NOT EXISTS (
            SELECT 1 
            from mytable 
            WHERE
                customerid = t1.customerid 
                AND date < t1.date
                AND date > t0.date
        )
GROUP BY t0.service

| service  | ReturnedWorse | ReturnedSame | ReturnedBetter || -------- | ------------- | ------------ | -------------- || Service1 | 0             | 2            | 0              || Service2 | 1             | 0            | 1              || Service3 | 1             | 0            | 0              |

---

From your comments, I understand that you want to take into account all future returns and not only the next one. This eliminates the need for a correlatead subquery, and actually yields your expected output. See this db fiddle :

SELECT
    t0.service,
    SUM(CASE WHEN t1.scorebefore < t0.scorebefore THEN 1 ELSE 0 END) AS ReturnedWorse,
    SUM(CASE WHEN t1.scorebefore = t0.scorebefore THEN 1 ELSE 0 END) AS ReturnedSame,
    SUM(CASE WHEN t1.scorebefore > t0.scorebefore THEN 1 ELSE 0 END) AS ReturnedBetter
FROM 
    mytable t0
    INNER JOIN mytable t1
        ON  t0.customerid = t1.customerid
        -- AND t0.service = t1.service
        AND t0.date < t1.date
GROUP BY t0.service

|服务 |返回更糟 |返回相同 |返回更好 |
| -------- | ------------- | ------------ | -------------- |
|服务1 | 0 | 2 | 1 |
|服务2 | 1 | 0 | 1 |
|服务3 | 2 | 0 | 0 |