sql - 用于 Microsoft Access 2013 的 SQL

Question

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

在计算特定服务的结果时，它应该查看任何服务类型的回报，而不仅仅是原始服务类型。（编辑：*它还应该查看所有未来的回报，而不仅仅是下一个或最近的*）。

它不需要经常运行，但是有 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))));

任何帮助将不胜感激。

标签： sqlms-access-2013

Answer 1

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

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

• t0：在表中选择一条记录（购买服务的客户）
• t1: 拉出同一客户下一次与INNER JOIN相关子查询签订任何服务时对应的记录（如果没有该记录，则不考虑初始记录）
• 将上一条记录的得分与当前记录进行比较
• 按服务 ID 对结果进行分组

您可以在这个 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

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

---

从您的评论中，我了解到您想要考虑所有未来的回报，而不仅仅是下一个回报。这消除了对相关子查询的需要，并且实际上产生了您的预期输出。看到这个数据库小提琴：

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 |