基于SQL或Power查询中的列值的复杂行操作

Question

我有一个电话数据集。看起来像这样

如果有关某个成员的呼叫在“原始呼叫”后的30天内出现，则该呼叫被认为是回调。我需要一些逻辑或电源查询魔法来使用此逻辑来处理此数据集。因此，最终结果应该看起来像这样

​我以为我可以做些什么，但是事实证明这很困难，当我有超过200万个独特的案例钥匙时，重复项会杀死运行时间和超载内存。有什么建议吗？我更喜欢在Power查询编辑器中进行操作，但可以在SQL中进行操作。 plz，谢谢你。

Answer 1

我认为您可以在电源查询中执行此操作，但是我不知道它会以200万张记录运行。

可以通过明智地使用 table.buffer 函数来加速它。但是，先尝试一下。

进行合理的自我文献

• 该代码应为每个成员ID的成员ID
• 组，从使用所述逻辑创建的记录列表中创建一个表。
• 扩展表
• 标记要删除的行通过将日期列列转换为一个，并将适当的逻辑应用于日期和移位列来删除的行。
• 代码假定每个成员ID的日期按升序顺序排列。如果不是，则需要添加一个额外的排序步骤，

请尝试此M代码。 （更改源线以与您自己的数据源一致）。

编辑：
编辑的代码允许从初始调用

let

//Change next line to be congruent with your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Case key", type text}, {"Member ID", Int64.Type}, {"Call Date", type date}}),

//Group by Member ID
//  then create tables with call back date using the stated logic
       #"Grouped Rows" = Table.Group(#"Changed Type", {"Member ID"}, {
        {"Call Backs",(t)=>Table.FromRecords(
            List.Generate(
            ()=>[ck=t[Case key]{0}, cd=t[Call Date]{0}, cb = null, df=null, idx=0],
            each [idx] < Table.RowCount(t),
            each [ck=if Duration.Days(t[Call Date]{[idx]+1} - [cd]) < 30 
                        then [ck] else t[Case key]{[idx]+1},
                cd=if Duration.Days(t[Call Date]{[idx]+1} - [cd]) < 30 
                        then [cd] else t[Call Date]{[idx]+1},
                    cb = if Duration.Days(t[Call Date]{[idx]+1} - [cd]) < 30  
                            then t[Call Date]{[idx]+1} else null,
                    df = if Duration.Days(t[Call Date]{[idx]+1} - [cd]) < 30
                            then Duration.Days(t[Call Date]{[idx]+1} - [cd]) else null,
                    idx = [idx]+1],
            each Record.FromList({[ck],[cd],[cb],[df]},{"Case key","Call Date","Call Back Date", "Datediff"}))
        )}
        }),
    #"Expanded Call Backs" = Table.ExpandTableColumn(#"Grouped Rows", "Call Backs", 
        {"Case key", "Call Date", "Call Back Date", "Datediff"}, 
        {"Case key", "Call Date", "Call Back Date", "Datediff"}),

    #"Shifted Datediff" = Table.FromColumns(
        Table.ToColumns(#"Expanded Call Backs") & {
        List.RemoveFirstN(#"Expanded Call Backs"[Datediff]) & {null}},
            type table[Member ID=Int64.Type, Case key=text, Call Date=date, Call Back Date=date, Datediff=Int64.Type, shifted=Int64.Type ]),

    #"Filter" = Table.SelectRows(#"Shifted Datediff", each [shifted]=null or [Datediff]<>null),
    #"Removed Columns" = Table.RemoveColumns(Filter,{"shifted"})
in
    #"Removed Columns"

示例有多个回调

Answer 2

认为您可以使用铅功能来执行此操作。

这是小提琴

select 
  a.*, 
  LEAD(CallDate, 1) OVER (
    Partition by memberId 
    ORDER BY 
      CallDate
  ) AS "CallbackDate", 
  LEAD(CallDate, 1) OVER (
    Partition by memberId 
    ORDER BY 
      CallDate
  ) - a.calldate AS DateDiff 
from 
  mytable a