第二行至第七行的总和

Question

我正在尝试创建一个查询，该查询获取两个参数，预状态和状态。从这里开始，我想总结一下持续时间。这是我的桌子：

   Title_Id    Media_Info_Id Rank                 Title_Pre_Status_Name                              Title_Status_Name                                  duration    Activity_Date
----------- ------------- -------------------- -------------------------------------------------- -------------------------------------------------- ----------- -------------
49090       -1            1                    New Work Order                                     Annotate WO                                        0           2016-11-15
49090       -1            2                    Annotate WO                                        New Work Order                                     69309       2017-01-02
49090       -1            3                    New Work Order                                     Annotate WO                                        1           2017-01-02
49090       -1            4                    Annotate WO                                        Ingest WO                                          4           2017-01-02
49090       -1            5                    Ingest WO                                          QC WO                                              1353        2017-01-03
49090       -1            6                    QC WO                                              New Work Order                                     33390       2017-01-26
49090       -1            7                    New Work Order                                     Annotate WO                                        971         2017-01-27
49090       -1            8                    Annotate WO                                        Ingest WO                                          27665       2017-02-15

因此，例如，我得到了如下所示：

预状态=‘注释WO’和status = 'New Work Order‘

我应该拿回2-7行的和。有谁知道在没有太多内在选择的情况下这样做的好方法？

Answer 1

我要这样做：

SELECT 
    t.Title_Id,
    sum(duration) as duration,
    date = (select min(Activity_Date) from Dwh_Fact_Title_Activity_Ranks_V t1 where t.Title_Id = t1.Title_Id and t1.Title_Pre_Status_Name = @pre_Statuse)
FROM  Dalet_DWH.dbo.Dwh_Fact_Title_Activity_Ranks_V t
where
t.Rank>=(select 
            min(t2.Rank) 
         from Dalet_DWH.dbo.Dwh_Fact_Title_Activity_Ranks_V t2 
         where 
            t.title_id = t2.Title_Id 
            and 
            t2.Title_Pre_Status_Name = @pre_Statuse) 
and 
t.Rank<=(select 
            max(t2.Rank) 
        from Dalet_DWH.dbo.Dwh_Fact_Title_Activity_Ranks_V t2 
        where 
            t.title_id = t2.Title_Id 
            and 
            t2.Title_Status_Name = @status)
group by t.Title_Id

它能工作，但我认为运行时间太长了。

请随时帮助和改进算法。

感谢所有人

Answer 2

您需要从给定预状态的最小ID到给定状态的最大ID的所有记录。因此:找到最小ID；找到最大ID；获取记录。

select sum(duration)
from mytable where rank between
  (select min(rank) from mytable where title_status_name = @prestatus)
  and
  (select max(rank) from mytable where title_status_name = @status);

为了使查询快速运行，您应该拥有以下索引：

create index idx1 on mytable(title_status_name, rank); -- finds min and max rank for 
                                                       -- a status quickly

create index idx2 on mytable(rank, duration); -- finds the records based on rank quickly
                                              -- and contains the duration to add up

Answer 3

如果我正确地理解了你的逻辑，那么这样的事情应该会奏效：

select t.*
from t 
where t.rank >= (select min(t2.rank) from t t2 where t2.title_id = t.title_id and t2.Title_Pre_Status_Name = 'Annotate WO') and
      t.rank <= (select max(t2.rank) from t t2 where t2.title_id = t.title_id and t2.Title_Pre_Status_Name = 'New Work Order');