如何编写使用先前记录值计算值的查询？

Question

我正在尝试找出计算基于先前记录值的值的最佳方法。我确信这是可能的，我只是无法弄清楚。

考虑一个具有 StartTime 和 StartTime 的表。 结束时间。除了这些值之外还有 2 种不同类型的工资：OnDuty 和 OnDuty。 下班。

OnDuty = StartTime 和 EndTime 之间的所有时间。

OffDuty：上一个 EndTime 和当前 StartTime 之间的所有时间.

因此，要计算 OffDuty，我们必须采用最后一条记录的 EndTime 并减去当前记录的 StartTime。

示例：

• 上班工资 = $10/小时
• 下班工资 = $2/小时

条目 1：

StartTime: 1/1/2010 @ 8:00 AM
EndTime: 1/1/2010 @ 6:00 PM
Pay = $100 (10 hrs @ $10/hr)

条目 2：

StartTime: 1/2/2010 @ 8:00 AM
EndTime: 1/2/2010 @ 6:00 PM
Pay = $128 (10 hrs @ $10/hr) + (14 hrs @ $2/hr) 

条目 3：

StartTime: 1/3/2010 @ 8:00 AM
EndTime: 1/3/2010 @ 10:00 AM
Pay = $48 (2 hrs @ $10/hr) + (14 hrs @ $2/hr) 

您看到这个模式了吗？

Total Pay = $276 (Entry1.Pay + Entry2.Pay + Entry3.Pay)

基本上，我正在争论是否最好：

1. 将所有负担放在数据库上来计算每个详细信息行的该值，或者
2. 将前一个记录的 EndTime 与每行一起存储（即 PreviousEndTime？）

我喜欢选项1 更好。但是，我什至不知道如何在不先创建查找查询的情况下执行此操作（注意：我使用的是 SqlCE）。我严重依赖 LinqToSql，并且担心使用 Linq 的性能，因为可能需要计算数十万条“条目”记录才能为每个员工提供单个 TotalPay。对每个详细信息行执行查找会很痛苦！

另一方面，我觉得使用“PreviousEndTime”字段方法会涉及大量维护工作。用户可以返回并更改 EndTime，而更新下一个 记录的 PreviousEndTime 的想法对我来说听起来像是黑客……

有什么想法吗？有想法吗？解决方案？ :)

Answer 1

我强烈建议选择“将所有负担放在数据库上来计算每个详细信息行的该值”。 这就是数据库（及其查询语言）的用途。

在 Oracle 中，您将使用 LEAD 和 LAG。

http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

在 SQL Server 中，可能有一些解决方法，例如：

http://www.rafael-salas.com/2008/05/t-sql-lead-and-lag-functions.html

不确定 SQL 是否支持不过，服务器CE。

Answer 2

无需存储前一行的结束时间。您可以使用 Row_NUMBER() 排名函数计算与前几行的差异，并然后执行自连接。

Answer 3

答案：为了向大家通报这一点，我采用了不同的方法...

由于我使用的是 SQL CE 并且它不支持视图或超前/滞后函数，因此我决定添加我的表中添加了一个新的 PreviousEndTime 列。这是我能找到的唯一优雅的方式，可以让我轻松查询 Employee 的总计计算。这不是我首选的解决方案，但它对我来说工作正常...

这种方法的唯一缺点是，当用户在任何记录上更新其 EndTime 时，我必须进行快速查找查找下一条记录并更新PreviousEndTime值，以确保我的计算在查询时返回正确的值。

这是我现在正在做的一个快速伪代码片段：

public decimal GetTotalPay(Employee employee) {
   return (from row in _timeSheet.GetDetailRows(employee.Id)
      select ((row.StartTime - row.PreviousEndTime) * employee.OffDutyWage) +
             ((row.EndTime - row.StartTime) * employee.OnDutyWage)).Sum();
}

感谢您的所有反馈。