Proc Sql(在Sas中)“计算"的等价物是什么?SQL中的命令? [英] What is the equivalent of Proc Sql (in Sas) "Calculated" command in SQL?

问题描述

Proc SQL 中的语法Calculated"允许您使用在前面步骤中创建的变量的别名.

The syntax "Calculated" in Proc SQL allows you to use the alias of a variable that was created in the previous steps.

例如:

Select *
id,
sum(amount) as sum
from dataset
group by id
order by calculated sum;

SQL 中是否存在允许相同结果的等效语法?

Is there an equivalent syntax in SQL that would allow the same result?

谢谢！

推荐答案

您实际上可以在 order by 子句中使用列别名(至少在大多数数据库中).所以你可以这样做:

You can actually use column aliases in the order by clause (at least in most databases). So you can do:

Select id, sum(amount) as thesum
from dataset
group by id
order by thesum;

(请注意，sum 在某些数据库中可能是保留字，因此我使用了另一个别名.)

(Note that sum might a reserved word in some databases, so I'm using another alias.)

您更大的问题是 SAS 所做的重新合并"，因为您包含了非聚合列.在大多数数据库中，您会这样写:

Your bigger issue is the "remerging" that SAS does because you have included non-aggregated columns. In most databases, you would write:

Select d.*, sum(d.amount) over (partition by id) as thesum
from dataset d
group by id
order by thesum;

这篇关于Proc Sql(在Sas中)“计算"的等价物是什么?SQL中的命令?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！