sql - Summarise data by date with percentages using SQL PIVOT
问题描述
I have some data that looks something like this:
---------------------------------------------
ID |EVENTDATETIME |CATEGORY |SOMEPROPERTY
---------------------------------------------
1 |20/03/2020 14:30 |CAT A |1
2 |20/03/2020 16:10 |CAT B |1
3 |20/03/2020 09:14 |CAT A |1
4 |20/03/2020 10:26 |CAT A |0
5 |21/03/2020 11:31 |CAT B |1
6 |21/03/2020 10:26 |CAT B |0
7 |22/03/2020 11:31 |CAT A |1
---------------------------------------------
Essentially I need to (in SQL) pivot this data somehow in to something like this:
---------------------------------------------
DAY |MONTH |YEAR |TOTAL ITEMS |SOMEPROPERTY%
---------------------------------------------
20 |MARCH |2020 |4 |75%
21 |MARCH |2020 |2 |50%
22 |MARCH |2020 |1 |100%
---------------------------------------------
Where the SOMEPROPERTY% is the % of the total items that day where the property is set to 1 (it can be 1 or 0).
I may also need to add CATEGORY as an extra level so that the data is grouped by that too:
-------------------------------------------------------
DAY |MONTH |YEAR |CATEGORY |TOTAL ITEMS |SOMEPROPERTY%
-------------------------------------------------------
20 |MARCH |2020 |CAT A |3 |66%
20 |MARCH |2020 |CAT B |1 |100%
21 |MARCH |2020 |CAT B |2 |50%
22 |MARCH |2020 |CAT A |1 |100%
-------------------------------------------------------
Can anyone assist?
Needs to be a single SQL statement - I tried doing it using GROUP BY but it got quite messy and was giving odd results. I think it may be possible with PIVOT but I can't find a good example and it's not the easiest command to play with.
n.b. I know that this sort of stuff would normally be done in the presentation layer rather than directly in SQL but I don't have that option for where this is needed.
Thanks.
解决方案
Use conditional aggregation. I'm going to assume SQL Server for this purpose:
select convert(date, eventdatetime) as dte, category,
count(*) as total_items,
avg( someproperty * 1.0 ) as ratio
from t
group by convert(date, eventdatetime), category;
There is no need to put year/month/day in separate columns.
The equivalent function in Oracle would be trunc(eventdatetime)
. There is similar functionality in whatever database you are using.
推荐阅读
- java - 类中的 main() 与接口中的 main()
- php - 未定义索引:级别
- django - 中级 Django 2.0 管理操作页面未显示
- csv - 在 NetLogo 中导入 CSV 文件
- python - 将 cookies txt 文件加载到 Python 中
- java - 为什么有人会在 java 1.8 的接口中定义静态方法?
- c# - linux 中单声道上的 iso-2022-jp 字符集(Amazon AMI)
- c# - 即使存在 C# selenium,也无法在页面上找到此元素
- assembly - memcmp 忽略奇数位置的字符
- amazon-web-services - 如何在 AWS VPC 的私有子网中设置实例?