通过从子集中的列中查找唯一字符串来派生列 [英] Derive Column By Finding Unique String From Column in a Subset

问题描述

此问题背后的想法与此问题，并稍有变化.考虑以下数据

The idea behind this question is similar to this question with a slight change. Consider data below

实际输出

+-------------+--------+------+
|     Id      | Weight | Type |
+-------------+--------+------+
| 00011223344 |     35 | A    |
| 00011223344 |     10 | A    |
| 12311223344 |    100 | B    |
| 00034343434 |     25 | A    |
| 00034343434 |     25 | A    |
| 99934343434 |    200 | C    |
| 88855667788 |    100 | D    |
+-------------+--------+------+

派生输出

+-------------+-------------+--------+---------------+------+
|     Id      |  Actual ID  | Weight | Actual Weight | Type |
+-------------+-------------+--------+---------------+------+
| 00011223344 | 12311223344 |     35 |            35 | A    |
| 00011223344 | 12311223344 |     10 |            10 | A    |
| 12311223344 | 12311223344 |    100 |            55 | B    |
| 00034343434 | 99934343434 |     25 |            25 | A    |
| 00034343434 | 99934343434 |     25 |            25 | A    |
| 99934343434 | 99934343434 |    200 |           150 | C    |
| 88855667788 | 88855667788 |    100 |           100 | D    |
+-------------+-------------+--------+---------------+------+

我需要在上表中添加另一列，以针对列类型添加 Actual_Type .上表也可以通过下面的代码此处.

I need to add another column to table above to add Actual_Type against column type. Above table can be derived by following code also provided here.

select t.*,
       (case when id like '000%' then weight
             else weight - sum(case when id like '000%' then weight else 0 end) over (partition by actual_id)
        end) as actual_weight
from (select t.*,
             max(id) over (partition by stuff(id, 1, 3, '')) as actual_id
      from t
     ) t;

我想不出如何在varchar列类型上应用分区和最大值.

I can't think of how can I apply partition by and apply max on a column Type that is varchar.

所需的输出

+-------------+-------------+--------+---------------+------+-------------+
|     Id      |  Actual ID  | Weight | Actual Weight | Type | Actual Type |   
+-------------+-------------+--------+---------------+------+-------------+
| 00011223344 | 12311223344 |     35 |            35 |  A   | B           |   
| 00011223344 | 12311223344 |     10 |            10 |  A   | B           |   
| 12311223344 | 12311223344 |    100 |            55 |  B   | B           |   
| 00034343434 | 99934343434 |     25 |            25 |  A   | C           |   
| 00034343434 | 99934343434 |     25 |            25 |  A   | C           |   
| 99934343434 | 99934343434 |    200 |           150 |  C   | C           |
| 88855667788 | 88855667788 |    100 |           100 |  D   | D           |
+-------------+-------------+--------+---------------+------+-------------+

修改:我无法在 nvarchar 的 Type 列上应用 min 或 max .我想对 Actual ID 列进行分区，并在 Type 列中找到唯一的值，即对于第一3行的 B .接下来的3是C.

I can not apply min or max over column Type which is nvarchar. I want to partition over column Actual ID and find the value which is unique in column Type i.e. B for 1st 3 rows. For next 3 it is C.

推荐答案

使用 FIRST_VALUE()窗口函数:

SELECT t.Id, t.Weight, t.Type, t.actual_id, t.actual_weight,
       FIRST_VALUE(Type) OVER (PARTITION BY actual_id ORDER BY counter) AS actual_type
FROM (
  SELECT t.*,
         CASE 
           WHEN id like '000%' THEN weight
           ELSE weight - SUM(CASE WHEN id LIKE '000%' THEN WEIGHT ELSE 0 END) OVER (PARTITION BY actual_id)
         END AS actual_weight,
         COUNT(*) OVER (PARTITION BY actual_id, Type) counter
  FROM (
    SELECT t.*,  MAX(id) OVER (PARTITION BY STUFF(id, 1, 3, '')) AS actual_id
    FROM t
  ) t
) t;

请参见演示.

这篇关于通过从子集中的列中查找唯一字符串来派生列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！