英文:
finding percentages between 2 different columns in sql
问题
我创建了这个查询：
select first_price, last_price, cast((sum(1 - (first_price / nullif(last_price,0)))) as double) as first_vs_last_percentages
from prices
group by first_price, last_price
having first_vs_last_percentages >= 0.1
不幸的是，在first_vs_last_percentages列中出现了错误的数据
| ID | first_price | last_price | first_vs_last_percentages |
|---|---|---|---|
| 1 | 10 | 11 | 1-(10/11) = 1.0 |
| 2 | 66 | 68 | 1-(66/68) = 1.0 |
应该返回以下输出：
| ID | first_price | last_price | first_vs_last_percentages |
|---|---|---|---|
| 1 | 10 | 11 | 1-(10/11) = 0.0909 |
| 2 | 66 | 68 | 1-(66/68) = 0.0294 |
如果有人有一个好的解决方案，并且它是presto语法，那将是非常棒的。
英文:
I was create this query:
select first_price, last_price, cast((sum(1 - (first_price / nullif(last_price,0)))) as double) as first_vs_last_percentages
from prices
group by first_price, last_price
having first_vs_last_percentages >= 0.1
unfortunately this is my wrong data in first_vs_last_percentages col
| ID | first_price | last_price | first_vs_last_percentages |
|---|---|---|---|
| 1 | 10 | 11 | 1-(10/11) = 1.0 |
| 2 | 66 | 68 | 1-(66/68) = 1.0 |
It was supposed to return this output:
| ID | first_price | last_price | first_vs_last_percentages |
|---|---|---|---|
| 1 | 10 | 11 | 1-(10/11) = 0.0909 |
| 2 | 66 | 68 | 1-(66/68) = 0.0294 |
if someone has a good solution and it will be in presto syntax it will be wonderful.
答案1
得分: 0
似乎您又遇到了整数除法的情况（将整数转换为双精度有点晚），更新查询以更改除数或被除数的类型（例如，通过将其中一个乘以 1.0，这比转换为 double 稍短）：
select -- ...
, sum(1 - (first_price * 1.0) / nullif(last_price, 0)) first_vs_last_percentages
from ...
附言：
您的查询有点奇怪，不确定为什么在这里需要分组和 sum。
英文:
It seems you got struck by another case of integer division (your cast to double is a bit late), update the query so the divisor or dividend type changes (for example by multiplying one of them by 1.0 which is a bit shorter then cast to double):
select -- ...
, sum(1 - (first_price * 1.0) / nullif(last_price, 0)) first_vs_last_percentages
from ...
P.S.
Your query is a bit strange, not sure why do you need grouping and sum here.
答案2
得分: 0
这取决于你所使用的数据库引擎。通常，大多数查询混淆都依赖于概念上的错误或语法错误。在任一种情况下，它试图操作一个行百分比，即100.0*(last-first)/first。这意味着你可以省略group by和having，因为我们绝不能按双倍值分组，而应该按它们所属的区间进行分组。
select
first_price,
last_price,
CASE
WHEN first_price = 0 THEN NULL
ELSE (last_price-first_price)/first_price
end as first_vs_last_percentage
from prices
英文:
It depends on which database engine you work upon. Typically, most query confusion rely on either conceptual or syntatic mistakes. In either one or the other cases, it seek to operate a row-percentage double 100.0*(last-first)/first. It means, you can drop the group by and having, since we MUST NOT group by double values, rather intervals they belong.
select
first_price,
last_price,
CASE
WHEN first_price = 0 THEN NULL
ELSE (last_price-first_price)/first_price
end as first_vs_last_percentage
from prices
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论