如何包含不属于分组依据的列 [英] How to include column which is not a part of group by

问题描述

如何通过 v_id 来获取 w_cost 的最大值，并且最终结果集应包括 av_id 。

How to get max of w_cost by v_id and also the final result set should include 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 。最终结果应为：

If w_cost is NULL, r_cost should be taken. The final result should be:

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;

推荐答案

窗口函数：

这是为 https://www.postgresql.org/docs/current/static/tutorial-window.html

请参见 db<>小提琴

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

A： COALESCE 给出列表中的第一个非 NULL 值。因此，如果 w_cost 为 NULL ，则会采用 r_cost 。

A: COALESCE gives the first not NULL value in the list. So if w_cost is NULL, r_cost will be taken.

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

B: The window function MAX() gives the max value in the partition of v_id. The max function ueses the same COALESCE clause as in (A)

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

C: The WHERE clause filters the row where max equals the current value of w_cost.

如果在我的示例中，有更多行具有相同的 MAX 值，您将全部获得。如果只需要其中之一，则可以在分区中添加一列，以使窗口更加精确。或者，您可以按顺序订购某商品，然后选择第一个，或者通过 DISTINCT ON 或多或少地选择一个。

If there are more rows with the same MAX value in my example you get all of them. If you just want one of them then you can add a column to the partition to make the window more precise. Or you can order by something and just take the first one or you take a more or less random one by DISTINCT ON.

DISTINCT ON：

具有 DISTINCT ON 您可以过滤特殊列的不同行（而常规 DISTINCT 会查看所有列）。由于没有任何 ORDER BY 子句的结果集可能是非常随机的，因此应按v_id和最终成本（最大的优先顺序（ DESC ）；使用上述 COALESCE 函数计算）。然后 DISTINCT 进入第一行。

With DISTINCT ON you can filter the distinct row for special columns (whereas the normal DISTINCT looks at all columns). Because a result set without any ORDER BY clause can be very random, it should be sorted by v_id and the final cost (greatest first (DESC); calculated with the COALESCE function as stated above). Then the DISTINCT takes the first row.

db<>小提琴

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

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

A: COALESCE as mentioned in the window function section.

B：命令先获取想要的行。

B: Ordering to get the wanted row first.

C：为每个不同的<$ DISTINCT ON 过滤器c $ c> v_id 第一行。

C: DISTINCT ON filters for every distinct v_id the first row.

这篇关于如何包含不属于分组依据的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！