按行应用分区时的情况 [英] Case when over partition by applied per row

问题描述

我的桌子看起来像这样:

My table looks like this:

|--------|--------|--------|--------|---------|---------|
|  num_1 |  num_2 |  num_3 |level_1 | level_2 | level_3 |
|--------|--------|--------|--------|---------|---------|
|  1111  |  3333  |  4444  |  false |  false  |  false  |
|--------|--------|--------|--------|---------|---------|
|  1111  |  3333  |  5555  |  false |  false  |  false  |
|--------|--------|--------|--------|---------|---------|
|  1111  |  6666  |  null  |  false |  false  |  false  |
|--------|--------|--------|--------|---------|---------|
|  1111  |  7777  |  8888  |  false |  true   |  false  |
|--------|--------|--------|--------|---------|---------|

因此表格如下:

|--------|--------|--------|--------|---------|---------|-------------|
|  num_1 |  num_2 |  num_3 |level_1 | level_2 | level_3 |   result    |
|--------|--------|--------|--------|---------|---------|-------------|
|  1111  |  3333  |  4444  | false  |  false  |  false  |    3333     |
|--------|--------|--------|--------|---------|---------|-------------|
|  1111  |  3333  |  5555  | false  |  false  |  false  |    3333     |
|--------|--------|--------|--------|---------|---------|-------------|
|  1111  |  6666  |  null  | false  |  false  |  false  |    6666     |
|--------|--------|--------|--------|---------|---------|-------------|
|  1111  |  7777  |  8888  | false  |  true   |  false  |    8888     |
|--------|--------|--------|--------|---------|---------|-------------|

编辑

这是一个简化的示例，但这是此表的工作方式:

Edit

This is a simplified example, but here is how this table works:

• num_x列将ID引用到另一个表中的元素，该表可能具有也可能没有我们正在寻找的特定属性.
• level_x列表示具有相同x值的列num_x是否具有将某些属性设置为true的功能. 这些条目表示不具有该属性的元素的层次结构:

• The num_x columns reference the ID to an element in another table, which might or might not have a certain attribute we're looking for.
• The level_x columns represents whether the column num_x with the same x value has that certain attribute set to true. These entries represent a hierarchy of elements which do or do not have that attribute:

1111 ✘ > 3333 ✘ > 4444 ✘
                 > 5555 ✘
       > 6666 ✘
       > 7777 ✓ > 8888 ✘

我的目标是遍历整个表，并为前一行没有子级别且具有true值的每一行找到最低级别. 例如，对于前几行，由于所有行都是1111的子级，并且该1111具有其属性设置为true的子级7777，因此所有其他子级与将是他们自己的行的结果，除非他们的子级属性也设置为true. 由于7777的属性设置为true，因此此行的结果是其直接子级8888.如果没有孩子，则该行将不需要结果.

My goal is to go through the whole table, and find the lowest level for each row at which the previous level has no child level with a true value. For example, for the first few rows, since all rows are children of 1111 and that 1111 has a child, 7777, with its attribute set to true, all other children at the same level as 7777 would be the result of their own row, unless they do have children with their attribute set to true as well. Since 7777 has its attribute set to true, the result of this row is its immediate child, 8888. If it did not have a child, the row would not need a result.

推荐答案

以下简单逻辑返回您指定的值.目前尚不清楚这是否是您的实际意图:

The following simple logic returns the values you specify. It is not clear if this is what you actually intend:

select (case when not level_2 then num_3
             else num_2
        end) 

我的最佳猜测是您需要一个相对简单的case表达式.我只是不清楚您真正需要什么.

My best guess is that a relatively simple case expression is what you need. I am just unclear on what you really need.

这篇关于按行应用分区时的情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！