问生成多个计数列的SQL
EN

Stack Overflow用户

提问于 2015-04-01 17:08:08

回答 1查看 193关注 0票数 1

我开发了一些SQL来生成本地票证数据库表( Server 2008)上的统计信息。正如您从我的代码中看到的，我希望从票证中选择，加入组以获得组名，按组代码以及年/月进行分组。

我想要创建总数(计数)，多少票是开放的，关闭的，关闭的sla (过期的日期)之外，最终SLA%。

但是，这段代码可以工作，但我不喜欢对所有嵌套的代码(选择计数)进行编码；这似乎不是一种适用于多次重扫描的好策略。

是否有更好的设计从表上的单个选择生成多个“计数”列.还是这是标准做法？

select  g.group_name as [Group],
        year(tm.date_open) as Year,
        month(tm.date_open) as Month,
        COUNT(*) as [Tickets Opened],

        (select COUNT(*)
         from TICKETS tm2
         where tm2.group_code = tm.group_code
         and year(tm2.COMPLETION_DATE) = year(tm.date_open)
         and month(tm2.COMPLETION_DATE) = month(tm.date_open)
        ) as [Tickets Closed],

        (select COUNT(*)
         from TICKETS tm2
         where tm2.group_code = tm.group_code
         and year(tm2.COMPLETION_DATE) = year(tm.date_open)
         and month(tm2.COMPLETION_DATE) = month(tm.date_open)
         and tm2.[COMPLETION_DATE] <= tm2.[DUE_DATE:]
        ) as [Closed Within SLA],   

        (select COUNT(*)
         from TICKETS tm2
         where tm2.group_code = tm.group_code
         and year(tm2.COMPLETION_DATE) = year(tm.date_open)
         and month(tm2.COMPLETION_DATE) = month(tm.date_open)
         and tm2.[COMPLETION_DATE] > tm2.[DUE_DATE:]
        ) as [Closed Outside SLA]   --service level agreement


from TICKETS tm
left join GROUPS g on g.group_code = tm.group_code
where g.group_code in ('techs', 'reps', 'phone')

and year(tm.date_open) = 2015
--and month(tm.date_open) = 3 -- specific month

group by tm.group_code, g.group_name, year(tm.date_open), month(tm.date_open)

order by g.group_name, year(tm.date_open), month(tm.date_open)

我还想添加SLA%列，该列将(在SLA /票证关闭内关闭)* 100。但是，正如我从当前设计中看到的那样，我必须为另一列添加冗余嵌套选择，例如.

(
    cast((select COUNT(*)
     from TICKETS tm2
     where tm2.group_code = tm.group_code
     and year(tm2.COMPLETION_DATE) = year(tm.date_open)
     and month(tm2.COMPLETION_DATE) = month(tm.date_open)
     and tm2.[COMPLETION_DATE] <= tm2.[DUE_DATE:]
    ) as decimal) /
    (select COUNT(*)
     from TICKETS tm2
     where tm2.group_code = tm.group_code
     and year(tm2.COMPLETION_DATE) = year(tm.date_open)
     and month(tm2.COMPLETION_DATE) = month(tm.date_open)
    )
) * 100 as [SLA%]

sql

sql-server-2008

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-04-01 17:57:36

是的，您可以比一堆子查询做得更好。在单个聚合查询中，可以通过计算满足条件时计算为1的各种表达式的SUM()来获得满足不同条件的行的单独计数，否则则为零。或者，您也可以对希望计数的行计算为非NULL的NULL表达式。例如，看起来这可能与您所追求的目标非常接近：

SELECT
  g.group_name AS [Group],
  year(tm.date_open) as Year,
  month(tm.date_open) as Month,
  COUNT(*) AS [Tickets Opened],
  COUNT(tm.completion_date) AS [Tickets Closed],
  SUM(CASE WHEN tm.completion_date <= [DUE_DATE:] THEN 1 ELSE 0 END)
    AS [Closed Within SLA],
  SUM(CASE WHEN tm.completion_date > [DUE_DATE:] THEN 1 ELSE 0 END)
    AS [Closed Outside SLA],   --service level agreement
  CAST(SUM(CASE WHEN tm.completion_date <= [DUE_DATE:] THEN 1 ELSE 0 END)
    AS decimal) / COUNT(tm.completion_date)) AS [SLA%],
FROM
  tickets tm
  LEFT JOIN GROUPS g
    ON g.group_code = tm.group_code
WHERE
  g.group_code in ('techs', 'reps', 'phone')
  and year(tm.date_open) = 2015
  -- and month(tm.date_open) = 3 -- specific month
GROUP BY
    tm.group_code,
    g.group_name,
    year(tm.date_open),
    month(tm.date_open)
ORDER BY
    g.group_name,
    year(tm.date_open),
    month(tm.date_open)

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/29396741

复制

相似问题

问生成多个计数列的SQL
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问生成多个计数列的SQLEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问生成多个计数列的SQL
EN