SQL百分比优雅
我刚刚开始熟悉SQL,我发现语言很简单,但也有时非常混乱。例如借此声明SQL百分比优雅
SELECT transaction.ID,
dt,
bundles.package_name as "Package Name",
amount_usd,
unit_sales,
CASE
WHEN sum(amount_usd) = 0 THEN 0
ELSE round(amount_usd*100/ (SELECT sum(amount_usd) FROM transaction
WHERE (transaction.code = 'AA')
AND (transaction.country_code = 'US')
AND (dt BETWEEN '2016-01-01' and '2016-01-04')),2)
END as percentage
FROM transaction
JOIN bundles
ON transaction.package_id= cast(bundles.id as INT)
WHERE (transaction.code = 'AA')
AND (transaction.country_code = 'US')
AND (dt BETWEEN '2016-01-01' and '2016-01-04')
GROUP BY transaction.ID, dt, "Package Name"
ORDER BY transaction.ID
limit 1000
我发现浪费一点点不得不重复SELECT加上只是正确计算的百分比。有没有更优雅的解决方案来避免这种情况?
下面是我如何修复它。
如果你想在总量的百分数,你不一定需要嵌套
SELECT t.ID, dt, b.package_name as "Package Name",
SUM(amount_usd) as amount_usd, SUM(unit_sales) as unit_sales,
SUM(amount_usd) * 1.0/SUM(amount_usd) OVER() as ratio
FROM transaction t JOIN
bundles b
ON t.package_id = cast(b.id as INT)
WHERE (t.code = 'AA') AND
(t.country_code = 'US') AND
(dt BETWEEN '2016-01-01' and '2016-01-04')
GROUP BY t.ID, dt, "Package Name"
ORDER BY t.ID
LIMIT 1000;
如果你想收入超过每天总的比你做
SELECT t.ID, dt, b.package_name as "Package Name",
SUM(amount_usd) as amount_usd, SUM(unit_sales) as unit_sales,
SUM(amount_usd) * 1.0/SUM(amount_usd) OVER (PARTITION BY dt) as ratio
FROM transaction t JOIN
bundles b
ON t.package_id = cast(b.id as INT)
WHERE (t.code = 'AA') AND
(t.country_code = 'US') AND
(dt BETWEEN '2016-01-01' and '2016-01-04')
GROUP BY t.ID, dt, "Package Name"
ORDER BY t.ID
LIMIT 1000;
最SQL的方言支持ANSI标准窗口函数。另外,格式化通常在应用程序中稍后处理。
所以,这通常被写为:
SELECT t.ID, dt, b.package_name as "Package Name",
SUM(amount_usd) as amount_usd, SUM(unit_sales) as unit_sales,
SUM(amount_usd) * 1.0/NULLIF(SUM(SUM(amount_usd)) OVER()) as ratio
FROM transaction t JOIN
bundles b
ON t.package_id = cast(b.id as INT)
WHERE (t.code = 'AA') AND
(t.country_code = 'US') AND
(dt BETWEEN '2016-01-01' and '2016-01-04')
GROUP BY t.ID, dt, "Package Name"
ORDER BY t.ID
LIMIT 1000;
在您的查询的其他问题:
- 所有符合条件的列名时查询有多个表。
- 使用表缩写作为表别名 - 写和读更容易。
- 不同表格上的
JOIN
键应该是相同的类型。事实上,他们应该被宣布为适当的外键关系。 - 您错过了
SELECT
中的某些聚合函数。确保只有未聚集的列是GROUP BY
密钥。
如果您的数据库供应商不支持ANSI标准功能,那么您应该采取这些措施。
我假设你的意思是“*当一个**查询**有多个表*”不“*当一个表有多个表时*” –
我得到“集合函数调用不能嵌套” –
@PasqualeSada。 。 。它是有效的ANSI标准SQL。如果您对某个特定数据库有疑问,则应该使用您正在使用的数据库标记问题。 –
用你正在使用的数据库标记你的问题。 –
@GordonLinoff added –