代码之家 › 专栏 › 技术社区 › rism

如何编写存储过程以返回按“X”周块分组的事件计数?

grouping sql-server-2008 sql-server-2005 sql

rism · 技术社区 · 14 年前

我有一个记录员工与客户之间市场互动的表格。相关字段将是customerid、date和customer type。

我希望能够获取返回的数据,并创建一个a、b和c系列,其中包含按5周块分组的市场交互计数。

 double[] byValues = { 14, 16, 18, 19, 15, 18, 19, 14, 15 };

 string[] bxValues = { "50", "45", "40", "35", "30", "25", "20", "15", "10" };

可能是A系列。一旦我有了我的A,B和C系列,我就可以把它们输入一个叠加的图表。

我最初的想法是查询一次并创建一个临时表,其中包含一个额外的字段,该字段由一个if语句填充,其中包含一些数学运算,该语句根据记录的日期计算记录的块秩。然后我可以用其他条件的分组计数来重新查询。但我真的不确定完成第一步最有效的方法是什么。这似乎是一种我可能会犯严重错误的事情,也许是数量级的。

更新:

SELECT  mi1.[CAQ] AS CAQ, FLOOR(DATEDIFF(d, mi1.IDate, CONVERT(datetime, '11/07/2010', 103))/5) AS X_AXISBLOCK 
INTO #myTempTable
FROM [ql10_crm].[MarketInteraction] mi1 INNER JOIN [ql10_crm].[TerritoryCustomer] 
ON mi1.[CustomerId] =[ql10_crm].[TerritoryCustomer].[CustomerId] 
WHERE mi1.[CAQ] IN(1,2,4) AND 
[ql10_crm].[TerritoryCustomer].[TerritoryId] IN(19) AND (mi1.[IDate] BETWEEN CONVERT(datetime, '18/06/2009', 103) AND  CONVERT(datetime, '11/07/2010', 103));

SELECT CAQ, X_AXISBLOCK , COUNT([CAQ])
FROM #myTempTable
GROUP BY CAQ, X_AXISBLOCK 
ORDER BY CAQ, X_AXISBLOCK;

不确定它是如何表现或灵活的比较答案。必须进行测试。

2 回复 | 直到 14 年前

Tom 14 年前

只是一个想法,但在我看来,您可能可以用纯SQL(通常更可取)来实现这一点,而不是诉诸于存储过程。

看一下你可用的分析函数 here

举个例子,你会看到这样的东西(决不是一个完整的解决方案-只是一个真正的指针):

SELECT a.custid,
       a.weekblock,
       COUNT(a.custid) OVER (PARTITION BY a.weekblock, a.custid)
  FROM (SELECT x.custid, DATEPART(wk, x.date) / 5 AS weekblock
          FROM market x) a

如果您提供有关表结构的更多信息,则可能会得到更好的解决方案。

Martin Smith 14 年前

DECLARE @X INT
SET @X =5

DECLARE @endDate DATETIME
DECLARE @startDate DATETIME

SET @endDate = '2010-07-18'
SET @startDate = '2009-07-18'

DECLARE @EndDateDays INT
SET @EndDateDays = CAST(@endDate AS INT);



WITH MarketInteractions AS
(
SELECT 1 AS customerid, CAST('2010-07-18' AS DATETIME) AS [DATE], 'a' AS CustomerType
UNION ALL
SELECT 1 AS customerid, CAST('2010-06-18' AS DATETIME) AS [DATE], 'b' AS CustomerType
UNION ALL
SELECT 1 AS customerid, CAST('2010-05-18' AS DATETIME) AS [DATE], 'a' AS CustomerType

),
MarketInteractions2 AS 
(
SELECT 1+ ((CAST(@endDate - [DATE] AS INT)) / (@X * 7)) AS Period, customerid, CustomerType
FROM MarketInteractions
WHERE [DATE] BETWEEN @startDate AND @endDate AND CustomerType IN ('a','b','c')
)

/* Possibly with a join on a numbers table to get periods with zero interactions if 
 that is possible*/

SELECT 
Period, 
COUNT(CASE WHEN CustomerType = 'a' THEN 1 END) AS a,
COUNT(CASE WHEN CustomerType = 'b' THEN 1 END) AS b,
COUNT(CASE WHEN CustomerType = 'c' THEN 1 END) AS c
FROM MarketInteractions2
GROUP BY Period,  CustomerType