如何包含不属于分组依据的列

Question

如何通过v_id获得最大w_cost，最终结果集应包括av_id。

s_id sg_id  r_cost  w_cost  av_id v_id
123  100    0.50    1.00    1     333
123  105    0.75    0.50    2     333
123  330    2.00    Null    3     888

如果w_cost为 NULL，则应采用r_cost。最终结果应该是：

s_id v_id   w_cost  av_id
123  333     1.00   1
123  888     2.00   3

基本查询为

SELECT
t.s_id,
sv.v_id,
sv.w_cost,
CASE
WHEN sv.w_cost IS NULL THEN
sv.r_cost::numeric
ELSE sv.w_cost::numeric
END AS cost
FROM test t
INNER JOIN stra_ven sv tmad ON
t.s_id = sv.s_id 
GROUP BY t.s_id,sv.v_id,sv.w_cost;

Answer 1

窗口函数：

这就是窗口函数的 https://www.postgresql.org/docs/current/static/tutorial-window.html

查看数据库<>小提琴

SELECT 
s_id, v_id, w_cost, av_id
FROM
(SELECT 
s_id,
v_id,
av_id,
COALESCE(w_cost, r_cost) as w_cost,                                    -- A
MAX(COALESCE(w_cost, r_cost)) OVER (PARTITION BY v_id) as max_w_cost   -- B
FROM testdata) s
WHERE 
max_w_cost = w_cost                                                        -- C

答：COALESCE给出列表中第一个不是NULL值。因此，如果w_costNULL，r_cost将被采取。

B：窗口函数MAX()给出v_id分区中的最大值。max 函数使用与 (A( 中相同的COALESCE子句

C：WHERE子句过滤max等于当前值w_cost的行。

如果我的示例中有更多行具有相同的MAX值，您将获得所有行。如果您只需要其中之一，则可以向分区添加一列以使窗口更精确。或者您可以按某物订购并只拿第一个，或者您或多或少地随机取一个DISTINCT ON.

---

在以下方面有所不同：

使用DISTINCT ON您可以过滤特殊列的不同行(而普通DISTINCT查看所有列(。因为没有任何ORDER BY子句的结果集可能非常随机，所以它应该按v_id和最终成本(最大优先(DESC(;如上所述使用COALESCE函数计算(进行排序。然后DISTINCT占据第一行。

数据库<>小提琴

SELECT DISTINCT ON (v_id)                  -- C
s_id, v_id, cost as w_cost, av_id
FROM
(SELECT 
s_id,
v_id,
av_id,
COALESCE(w_cost, r_cost) as cost   -- A
FROM testdata
ORDER BY v_id, cost DESC) s           -- B

答：COALESCE如窗口功能部分所述。

B：排序以首先获取想要的行。

C：DISTINCT ON第一行的每个不同v_id过滤器。

Answer 2

下面的 SQL 可能有效：

with my_table as (
select 
123 as s_id,
100 as sg_id,
0.50 as r_cost,
1.00 as  w_cost,
1 as av_id,
333 as v_id
union all
select 
123 as s_id,
105 as sg_id,
0.75 as r_cost,
0 as  w_cost,
2 as av_id,
333 as v_id
union all 
select 
123 as s_id,
330 as sg_id,
1.00 as r_cost,
Null as  w_cost,
3 as av_id,
888 as v_id
),
w_r_cost_table as (
select t.*,
case 
when t.w_cost is not null then t.w_cost
else t.r_cost
end as w_r_cost
from my_table t
),
grouped_table as (
select  
A.v_id, 
max(A.w_r_cost) as w_cost  
from w_r_cost_table A
group by A.v_id
)
select 
(select t.s_id from w_r_cost_table t where t.w_r_cost = g.w_cost and t.v_id = g.v_id) as s_id,
g.v_id,
g.w_cost,
(select t.av_id from w_r_cost_table t where t.w_r_cost = g.w_cost and t.v_id = g.v_id) as av_id
from grouped_table g

假设临时表my_table是源表。 首先，我们应该定义列w_cost的更正值。就像你的问题一样，我们不应该考虑nullw_cost的值。

为此，我创建了一个名为w_r_cost_table的临时表。使用"case when"，我们可以创建一个 if/else 子句，并将更正后的值放在一个名为w_r_cost的新列中。

然后，使用 temp 表w_r_cost_table，我们可以通过使用字段w_r_cost执行一个组，就像在grouped_table中所做的那样。

最后，只需在grouped_table中进行选择即可获得结果。

我在这个例子中使用了Postgres。