问题所在
Date OS TABLE: OSusage
2018-01-01 MacOS
2018-01-03 MacOS
2018-01-11 MacOS
2018-01-20 Windows
2018-01-21 Linux
2018-03-01 MacOS
2018-03-14 MacOS
2018-03-20 MacOS
2018-03-29 Windows
2018-05-10 MacOS
2017-10-10 MacOS
我想将上述数据总结为过去6个月的细目,如下所示:
Month & of MacOS
May 100%
March 75% (3/4*100)
January 60% (3/5*100)
这是我的问题的一个简化版本,这就是我所做的。
到目前为止我已经尝试过了。
DATENAME是我设法获得月份分解的方法,但即使尝试在子查询中再次使用它,我也很难找到一种计算MacOS的方法。
SELECT DATENAME(Month,OSusage.date)AS Months, OS
FROM OSusage
WHERE OSusage.Date >= DATEADD(MONTH, -6, GETDATE())
GROUP BY DATENAME(Month,OSusage.date)
对解决方案着迷&非常感谢任何帮助。干杯
发布于 2018-06-09 06:43:36
我会这样做的比率:
select year(o.date), datename(month, o.date),
avg(case when os = 'MacOS' then 100.0 else 0 end) as macos_ratio
from osuage o
group by year(o.date), datename(month, o.date)
order by min(o.date);
请注意,我包含了年份。也许您不希望年份被打破,但这是处理月份时的典型情况。
发布于 2018-06-09 06:57:37
这应该是可行的
declare @t table(dt date, OS varchar(20));
insert into @t values
('2018-01-01', 'MacOS'),
('2018-01-03', 'MacOS'),
('2018-01-11', 'MacOS'),
('2018-01-20', 'Windows'),
('2018-01-21', 'Linux'),
('2018-03-01', 'MacOS'),
('2018-03-14', 'MacOS'),
('2018-03-20', 'MacOS'),
('2018-03-29', 'Windows'),
('2018-05-10', 'MacOS'),
('2017-10-10', 'MacOS');
select a.yyyy, a.mm, 100.0*m.cnt/a.cnt as pct
from (select year(dt) as yyyy, month(dt) as mm, count(*) as cnt
from @t
group by year(dt), month(dt)
) a
join (select year(dt) as yyyy, month(dt) as mm, count(*) as cnt
from @t
where OS = 'MacOS'
group by year(dt), month(dt)
) m
on a.yyyy = m.yyyy
and a.mm = m.mm
order by yyyy, mm
https://stackoverflow.com/questions/50769194
复制相似问题