from_number呼叫to_number的頻率是多少？

SELECT tbl.*
FROM (
  VALUES[/span

  ( '1/02/2020'/span>, '21123456'/span>, '27564564'/span>, '1'/span>, '4'/span> )
  , ( '1/02/2020', '21123456', '27564564', '1', '4' )
  ,( '1/02/2020', '21123456', '21622222', '3', '1' )
  , ( '1/02/2020', '21633499', '21636363', '3', '3' )
  , ( '1/02/2020', '21633499', '21636363', '3', '3' )
  , ( '1/02/2020', '21633499', '21636363', '1', '3' )
  , ( '1/02/2020', '21633499', '21157157', '1', '2' )
  , ( '2/02/2020', '21633499', '21157157', '4', '2' )
  , ( '2/02/2020', '21123456', '27564564', '4', '4' )
  , ( '2/02/2020', '21123456', '27564564', '4', '4' )
  , ( '2/02/2020', '21123456', '27564564', '4', '4' )
  , ( '2/02/2020', '216223344', '27564564', '1', '1' )
  , ( '2/02/2020', '216223344', '21157157', '1', '1')
) tbl ([Date], [From_Number], [To_Number], [How_many_calls_to_num_per_day], [How_many_calls_to_num] ) 

我試過：

SELECT *, 
    , COUNT ([From_Number]) OVER. 
        (PARTITION BY [To_Number], [Date]
    ) AS [How_many_calls_to_num] 。       
FROM tbl

但是我沒有得到想要的結果，想知道需要做什么？

關于獲得每天的計數，我是否可以在儀表板中過濾日期？

我需要能夠找出誰給誰打電話（從--到數字），何時以及多長時間。

因此，我希望每個電話號碼的所有這些變數都在一行中，就像在：

[Date], [Date], [Date], [Date], [Date], [Date].

[Date], [From_Number], [To_Number], [How_many_calls]

我沒能用分組集得到想要的結果。 此外，我還需要在上面提到的一行中得到它，而不需要對資料集進行聚合。

是否有更好的方法來設定PARTITION BY查詢？

uj5u.com熱心網友回復：

你可以使用GROUPING SETS在兩個不同的組進行分組：to_number, date和to_number only（跨日期）。

;with cte_tbl as
(
SELECT tbl.*.
FROM (
  VALUES[/span

  ( '1/02/2020'/span>, '21123456'/span>, '27564564'/span>, '1'/span>, '4'/span> )
  , ( '1/02/2020', '21123456', '27564564', '1', '4' )
  ,( '1/02/2020', '21123456', '21622222', '3', '1' )
  , ( '1/02/2020', '21633499', '21636363', '3', '3' )
  , ( '1/02/2020', '21633499', '21636363', '3', '3' )
  , ( '1/02/2020', '21633499', '21636363', '1', '3' )
  , ( '1/02/2020', '21633499', '21157157', '1', '2' )
  , ( '2/02/2020', '21633499', '21157157', '4', '2' )
  , ( '2/02/2020', '21123456', '27564564', '4', '4' )
  , ( '2/02/2020', '21123456', '27564564', '4', '4' )
  , ( '2/02/2020', '21123456', '27564564', '4', '4' )
  , ( '2/02/2020', '216223344', '27564564', '1', '1' )
  , ( '2/02/2020', '216223344', '21157157', '1', '1')
) tbl ([Date], [From_Number], [To_Number], [How_many_calls_to_num_per_day], [How_many_calls_to_num]) 
)
SELECT to_number,[date] 。count(*) as NumberOfCalls from cte_tbl
group by 
grouping sets( (to_number,[date]), ([to_number]);

在下面的表格中，以NULL作為日期的那一行，是跨越to_number的日期。 to_number日期 呼叫次數 21157157 1/02/2020 1





21157157 ? 2/02/2020 2/02/2020 2 21157157 2/02/2020 2





21157157 21157157 NULL 3 3 3 3 3 3





21622222 1/02/2020 1/02/2020





NULL 1 1 1





216363631/02/2020





21636363NULL 3 3 3





27564564 ? 1/02/2020





275645642/02/2020? 4 27564564 2/02/2020 4





27564564 27564564 6 6 6

UPDATE正如評論中所問到的，感謝@charlieface，你可以按照下面的方法添加from_number。

SELECT from_number, to_number,[date]。count(*) as NumberOfCalls from cte_tbl
group by 
grouping sets( (from_number,to_number,[date]), (from_number,[to_number])。

標籤：

上一篇：如何合并OUTERJOIN和UNION的結果？

下一篇：根據所有地點的最新記錄計算平均數