独立事件的相关序列.计算时间交集

Question

我们正在构建一个PowerBI报告解决方案，我(好堆栈)解决了一个问题问题，业务提出了一个新的报告想法。因为我对PowerBI知之甚少，而且业务似乎想要相当复杂的报告，所以我不确定处理它的最佳方法。

我们有来自不同数据源的两个事件序列。它们都包含发生在车辆上的独立事件。一种描述车辆所在的位置，另一种描述事故事件，这些事件有事故的原因代码。企业想要报告在每个地点花费的时间，因为每个原因。车辆可以完全独立于事件发生的位置改变位置，而事件实际上是日期时间，并在一整天的随机点上发生。每种类型的事件都有一个启动/结束时间和一个vehicleID。

车辆定位事件

+------------------+-----------+------------+-----------------+----------------+
| LocationDetailID | VehicleID | LocationID |  StartDateTime  |  EndDateTime   |
+------------------+-----------+------------+-----------------+----------------+
|                1 |         1 |          1 |        2012-1-1 |      2016-1-1  |
|                2 |         1 |          2 |        2016-1-1 |      2016-4-1  |
|                3 |         1 |          1 |        2016-4-1 |      2016-11-1 |
|                4 |         2 |          1 |        2011-1-1 |      2016-11-1 |
+------------------+-----------+------------+-----------------+----------------+

车辆状态事件

+---------+---------------+-------------+-----------+--------------+
| EventID | StartDateTime | EndDateTime | VehicleID | ReasonCodeID |
+---------+---------------+-------------+-----------+--------------+
|       1 | 2012-1-1      | 2013-1-1    |         1 |            1 |
|       2 | 2013-1-1      | 2015-1-1    |         1 |            3 |
|       3 | 2015-1-1      | 2016-5-1    |         1 |            4 |
|       4 | 2016-5-1      | 2016-11-1   |         1 |            2 |
|       5 | 2015-9-1      | 2016-2-1    |         2 |            1 |
+---------+---------------+-------------+-----------+--------------+

不管怎么说，我是否可以将这两条流联系在一起，并计算每个位置每辆车的总时间？这似乎需要我能够将这两个事件联系起来--因此，在给定的ReasonCode中，位置的改变可能会部分发生。

计算实例ReasonCodeID 4

• VehicleID 1从2012-1-1到2016-1和2016-4-1到2016-11-1在ID 1的位置
• VehicleID 1在2016-1-1至2016-4-1的位置ID 2。
• VehcileID 1的ReasonCodeID 4从2015-1-1到2016-5-1

因此，位置1的第一个周期与365日的ReasonCodeID 4(2015-2016-1-1)相交。地点1的第二阶段与30天相交(2016-4-1至2016-5-1)。位置2与ReasonCodeID 4的91天相交(2016-1-1至2016-4-1 )

期望的产出如下。

+-----------+--------------+------------+------------+
| VehicleID | ReasonCodeID | LocationID | Total Days |
+-----------+--------------+------------+------------+
|         1 |            1 |          1 |        366 |
|         1 |            3 |          1 |        730 |
|         1 |            4 |          1 |        395 |
|         1 |            4 |          2 |         91 |
|         1 |            2 |          1 |        184 |
|         2 |            1 |          1 |        154 |
+-----------+--------------+------------+------------+

我已经创建了一个SQL花键，它显示了结构这里。

车辆有相关的表格，我相信企业会希望它们按车辆类别等进行分组，但是如果我能够理解如何在这种情况下计算交点，这将为其他报告提供依据。

Answer 1

我认为这个解决方案需要一个CROSS JOIN实现。两个表之间的关系是Many to Many，这意味着创建第三个连接LocationEvents和VehicleStatusEvents表的表，因此我认为在表达式中指定关系可能更容易。

我在两个表之间使用交叉连接，然后过滤结果，只得到两个表中VehicleID列相同的行。我还过滤了VehicleStatusEvents范围日期与LocationEvents范围日期相交的行。

过滤完成后，我将添加一个列来计算每个交叉口之间的天数。最后，对每个VehicleID、ReasonCodeID和LocationID的天数进行了总结。

为了实现交叉连接，您必须在两个表上重命名VehicleID、StartDateTime和EndDateTime。这是必要的，以避免矛盾的列名错误。

我将这些列重命名如下：

VehicleID：LocationVehicleID和StatusVehicleID

StartDateTime：LocationStartDateTime和StatusStartDateTime

EndDateTime：LocationEndDateTime和StatusEndDateTime

之后，您可以在Total Days度量中使用CROSSJOIN：

Total Days =
SUMX (
    FILTER (
        ADDCOLUMNS (
            FILTER (
                CROSSJOIN ( LocationEvents, VehicleStatusEvents ),
                LocationEvents[LocationVehicleID] = VehicleStatusEvents[StatusVehicleID]
                    && LocationEvents[LocationStartDateTime] <= VehicleStatusEvents[StatusEndDateTime]
                    && LocationEvents[LocationEndDateTime] >= VehicleStatusEvents[StatusStartDateTime]
            ),
            "CountOfDays", IF (
                [LocationStartDateTime] <= [StatusStartDateTime]
                    && [LocationEndDateTime] >= [StatusEndDateTime],
                DATEDIFF ( [StatusStartDateTime], [StatusEndDateTime], DAY ),
                IF (
                    [LocationStartDateTime] > [StatusStartDateTime]
                        && [LocationEndDateTime] >= [StatusEndDateTime],
                    DATEDIFF ( [LocationStartDateTime], [StatusEndDateTime], DAY ),
                    IF (
                        [LocationStartDateTime] <= [StatusStartDateTime]
                            && [LocationEndDateTime] <= [StatusEndDateTime],
                        DATEDIFF ( [StatusStartDateTime], [LocationEndDateTime], DAY ),
                        IF (
                            [LocationStartDateTime] >= [StatusStartDateTime]
                                && [LocationEndDateTime] <= [StatusEndDateTime],
                            DATEDIFF ( [LocationStartDateTime], [LocationEndDateTime], DAY ),
                            BLANK ()
                        )
                    )
                )
            )
        ),
        LocationEvents[LocationID] = [LocationID]
            && VehicleStatusEvents[ReasonCodeID] = [ReasonCodeID]
    ),
    [CountOfDays]
)

然后，在Power中，您可以使用以下度量构建一个矩阵(或任何其他可视化)：

​

​

如果您不完全理解度量值表达式，下面是T翻译：

SELECT
    dt.VehicleID,
    dt.ReasonCodeID,
    dt.LocationID,
    SUM(dt.Diff) [Total Days]
FROM 
(
    SELECT
        CASE
            WHEN a.StartDateTime <= b.StartDateTime AND a.EndDateTime >= b.EndDateTime  -- Inside range
               THEN DATEDIFF(DAY, b.StartDateTime, b.EndDateTime)
            WHEN a.StartDateTime > b.StartDateTime AND a.EndDateTime >= b.EndDateTime  -- |-----|*****|....|
               THEN DATEDIFF(DAY, a.StartDateTime, b.EndDateTime)
            WHEN a.StartDateTime <= b.StartDateTime AND a.EndDateTime <= b.EndDateTime  -- |...|****|-----|
               THEN DATEDIFF(DAY, b.StartDateTime, a.EndDateTime)
            WHEN a.StartDateTime >= b.StartDateTime AND a.EndDateTime <= b.EndDateTime  -- |---|****|-----
               THEN DATEDIFF(DAY, a.StartDateTime, a.EndDateTime)
        END Diff,
        a.VehicleID,
        b.ReasonCodeID,
        a.LocationID --a.StartDateTime, a.EndDateTime, b.StartDateTime, b.EndDateTime
    FROM LocationEvents a
        CROSS JOIN VehicleStatusEvents b
    WHERE a.VehicleID = b.VehicleID
        AND 
        (
            (a.StartDateTime <= b.EndDateTime)
                AND (a.EndDateTime >= b.StartDateTime)
        )
) dt
GROUP BY dt.VehicleID,
         dt.ReasonCodeID,
         dt.LocationID

注:在T中，您也可以使用INNER JOIN操作符。

如果这有帮助的话请告诉我。

Answer 2

select      coalesce(l.VehicleID,s.VehicleID)   as VehicleID
           ,s.ReasonCodeID
           ,l.LocationID

           ,sum
            (
                datediff
                (
                    day
                   ,case when s.StartDateTime > l.StartDateTime then s.StartDateTime else l.StartDateTime end
                   ,case when s.EndDateTime   < l.EndDateTime   then s.EndDateTime   else l.EndDateTime   end
                )
            )   as TotalDays

from                    VehicleLocationEvents   as l

            full join   VehicleStatusEvents     as s

            on          s.VehicleID =
                        l.VehicleID

                    and case when s.StartDateTime > l.StartDateTime then s.StartDateTime else l.StartDateTime end   <=
                        case when s.EndDateTime   < l.EndDateTime   then s.EndDateTime   else l.EndDateTime   end   

group by    coalesce(l.VehicleID,s.VehicleID)
           ,s.ReasonCodeID
           ,l.LocationID

或

select      VehicleID
           ,ReasonCodeID
           ,LocationID
           ,sum (datediff (day,max_StartDateTime,min_EndDateTime))  as TotalDays

from       (select      coalesce(l.VehicleID,s.VehicleID)   as VehicleID
                       ,s.ReasonCodeID
                       ,l.LocationID

                       ,case when s.StartDateTime > l.StartDateTime then s.StartDateTime else l.StartDateTime end   as max_StartDateTime
                       ,case when s.EndDateTime   < l.EndDateTime   then s.EndDateTime   else l.EndDateTime   end   as min_EndDateTime

            from                    VehicleLocationEvents   as l

                        full join   VehicleStatusEvents     as s

                        on          s.VehicleID =
                                    l.VehicleID
            ) ls

where       max_StartDateTime <= min_EndDateTime

group by    VehicleID
           ,ReasonCodeID
           ,LocationID