SQL-聚合函数-1193. 每月交易I
预备知识:
1. DATE_FORMAT(date, format):用于以不同的格式显示日期/时间数据,date参数是合法的日期,format规定日期/时间的输出格式。
DATE_FORMAT(date, format)
date:要格式化的日期值
format:指定日期输出格式的字符串
常见的日期格式包括:
%Y
: 四位年份。%m
: 两位月份。%d
: 两位日期。%H
: 24小时制的小时。%i
: 两位分钟。%s
: 两位秒。
举个例子:
SELECT column_name, DATE_FORMAT(date_column, '%Y-%m-%d') AS formatted_date FROM your_table;
解题思路:
我的错误写法
select date_format(trans_date, '%Y-%m') as month, country, count(case when state = 'approved' then 1 end) as approved_count count(month) as trans_count, count(amount) as trans_total_amount, count(case when state = 'approved' then amount end) as approved_total_amount from Transactions group by month, country;
正确的写法
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month, country, COUNT(*) AS trans_count, COUNT(IF(state = 'approved', 1, NULL)) AS approved_count, SUM(amount) AS trans_total_amount, SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount FROM Transactions GROUP BY month, country
现在让我们逐渐分析,哪里出了错误:
1. 以下两者是等价的,我们写的没有问题,在case表达式中,如果条件不符合,会默认返回null。
count(case when state = 'approved' then 1 end) as approved_count
COUNT(IF(state = 'approved', 1, NULL)) AS approved_count
2. 在数据库查询中,count(*)计算所有行的数量,包括null值的行。count(column_name)计算指定列中非null的数量,如果我们确切知道列中不包含null值,或者希望排除null,就可以用count(column_name)。
3. 不应该使用count(amount),因为我们想要计算加和,而不是amount行的数量。
4. 最后一个count同理,我们应该使用sum计算总和,以下二者等价,注意sql中没有“==”:
sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
注意这里sum中的case要加上一个else条件返回0,因为这里使用的是sum,不能出现null。
5. 可能会引起争议的地方是最后一行,用“month”进行分组。