在表中的一些连续行之后插入行的最佳做法是什么？

Question

我在一个表格中有以下数据：

REGION,CATEGORY,SUBCATEGORY,ItemName,ItemViews,sales,ItemViewspercentage
CE,Infotainment,Safe Exit Assist,Voice Commands,5,4,125.00%
EA,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
SO,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
WE,Infotainment,Safe Exit Assist,Voice Commands,5,5,100.00%
CE,Infotainment,Safe Exit Assist,Voice Recognition Tips,2,2,100.00%
EA,Infotainment,Safe Exit Assist,Voice Recognition Tips,1,1,100.00%
SO,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,2,150.00%
SW,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
WE,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
EA,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SO,Infotainment,Voice Recognition,Setting Clock,1,1,100.00%
WE,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SW,Infotainment,Voice Recognition,Setting Presets,2,1,200.00%
CE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
WE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
CE,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
CE,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
EA,KIA Drive Wise,Android Auto,Driver Attention Warning,2,3,66.67%
SO,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
SW,KIA Drive Wise,Android Auto,Driver Attention Warning,1,1,100.00%
WE,KIA Drive Wise,Android Auto,Driver Attention Warning,5,5,100.00%

我希望上述数据如下：

REGION,CATEGORY,SUBCATEGORY,ItemName,ItemViews,sales,ItemViewspercentage
CE,Infotainment,Safe Exit Assist,Voice Commands,5,4,125.00%
EA,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
SO,Infotainment,Safe Exit Assist,Voice Commands,2,1,200.00%
WE,Infotainment,Safe Exit Assist,Voice Commands,5,5,100.00%
Total,Infotainment,Safe Exit Assist,Voice Commands,14,11,78.57%
CE,Infotainment,Safe Exit Assist,Voice Recognition Tips,2,2,100.00%
EA,Infotainment,Safe Exit Assist,Voice Recognition Tips,1,1,100.00%
SO,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,2,150.00%
SW,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
WE,Infotainment,Safe Exit Assist,Voice Recognition Tips,3,3,100.00%
Total,Infotainment,Safe Exit Assist,Voice Recognition Tips,12,11,109.09%
EA,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
SO,Infotainment,Voice Recognition,Setting Clock,1,1,100.00%
WE,Infotainment,Voice Recognition,Setting Clock,3,1,300.00%
Total,Infotainment,Voice Recognition,Setting Clock,7,3,233.33%
SW,Infotainment,Voice Recognition,Setting Presets,2,1,200.00%
CE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
WE,Infotainment,Voice Recognition,Sound Settings,1,1,100.00%
Total,Infotainment,Voice Recognition,Sound Settings,2,2,100.00%
CE,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
Total,Infotainment,Voice Recognition,Vehicle Settings,1,1,100.00%
CE,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
EA,KIA Drive Wise,Android Auto,Driver Attention Warning,2,3,66.67%
SO,KIA Drive Wise,Android Auto,Driver Attention Warning,4,3,133.33%
SW,KIA Drive Wise,Android Auto,Driver Attention Warning,1,1,100.00%
WE,KIA Drive Wise,Android Auto,Driver Attention Warning,5,5,100.00%
Total,KIA Drive Wise,Android Auto,Driver Attention Warning,16,15,106.66%

我想在某些特定的行数之后插入一行。我应该使用SQL服务器中的哪个循环来完成我的工作？

Answer 1

您可以为此使用GROUPING SETS

SELECT
  REGION = CASE WHEN GROUPING(REGION) = 1 THEN 'Total' ELSE REGION END,
  CATEGORY,
  SUBCATEGORY,
  ItemName,
  ItemViews = SUM(ItemViews),
  sales = SUM(sales),
  ItemViewspercentage = FORMAT(SUM(ItemViews) * 1.0 / SUM(sales), 'P2')
FROM YourTable t
GROUP BY GROUPING SETS (
  (CATEGORY, SUBCATEGORY, ItemName, REGION),
  (CATEGORY, SUBCATEGORY, ItemName)
);

db<>fiddle