我有一个不同的 objects 表,随着时间的推移,对象会逐渐发展 . 一个对象由 object_number 标识,我们可以使用 object_line_number 跟踪它 . 并且对象的每个进化都具有一种状态 .
我想计算一些状态之间经过的时间 .
下面是我的一个object_number“us1”的表:
在 yellow 中是包含开始日期的行 . 如果(status_id = 0且(old_status <> 0或object_line_number = 1)且emergency_level = 1),则找到它们 .
在 green 中是包含结束日期的行 . 如果(status_id = 2,3,4,5和old_status = 0),则找到它们 .
表中不存在 old_status 列 . 这是前一行的状态(根据对象)line_number) . 我正在通过以下措施检索它:
old_status = CALCULATE (
MAX(fact_object[status_id]),
FILTER (
ALL(fact_object),
fact_object[object_line_number] = IF(fact_object[object_line_number]=1, fact_object[object_line_number], MAX (fact_object[object_line_number])-1)),
VALUES (fact_object[object_number]))
我处于 DirectQuery 模式,因此计算列中没有很多函数,这就是我使用Measures的原因 .
一旦完成,我希望能够为每个 green 行获取前一个 yellow 行的 date_modification .
在这个例子中,结果将是4/4然后1.这样我就可以计算当前绿行的 date_modification 与前一个黄行的 date_modification 之间的时差 .
所以我想添加一个名为 date_received 的新列,它是前一个黄色行的date_modification;
从那里,我只需要保留绿色行并计算 date_modification 和 date_received 之间的差异 .
我的最终计算实际上是这样的:
Result =(date_modification和date_received之间的日期差异的绿色行数<= 15分钟)/(DAY(date_modification)= DAY(date_received)的绿色行数)
但我不知道该怎么做 . 我已经尝试了 old_status 措施的相同精神来做到这一点:
date_received = CALCULATE (
MAX(fact_object[date_modification]),
FILTER (
ALL(fact_object),
(fact_object[object_line_number] = MAX (fact_object[object_line_number])-1) && MY OTHER FILTERS
),
VALUES (fact_object[object_number])
)
但没有成功 .
在SQL中,等价物将是这样的:
SELECT
SUM(CASE WHEN (DATEDIFF(MINUTE, T.date_received, T.date_planification) <= 15) THEN 1 ELSE 0 END) /
SUM(CASE WHEN (DAY(T.date_received) = DAY(T.date_planification)) THEN 1 ELSE 0 END) as result
FROM (
SELECT *, T.status_id as current_status,
LAG(T.date_modification) OVER(PARTITION BY T.object_number ORDER BY T.object_line_number) as date_received,
T.date_modification as date_planification
FROM
(
select *,
LAG (status_id) OVER(PARTITION BY object_number ORDER BY object_line_number) AS old_status
from dbo.fact_object
) AS T
WHERE ((T.status_id = 0 AND (T.old_status <> 0 OR T.object_line_number = 1) AND T.emergency_level = 1) OR (T.old_status = 0 AND T.status_id IN (2,3,4,5)))--974
) AS T
WHERE old_status = 0
(好吧,也许有更好的方法在SQL中做到这一点) .
我怎样才能做到这一点?
1 回答
在这种情况下,我首先按对象然后按对象行号(或修改日期)对表进行排序 - 但这些对于每一行似乎都不会改变 . )
复制表格
表1 - 添加从1开始的索引表2 - 添加从0开始的索引
从Table1,使用新的索引字段将其与table2合并
展开MergedStatus ID(重命名为NextStatusID)和Object(重命名为NextObject)字段 .
您现在可以创建一个新的条件列来比较两个状态字段 . 例如 . 状态2 =如果Object = NextObject,则[NextStatusID]否则为null