查询有条件地查找组的最大值

Question

我有一个表，其中有名称、组名和值。每个组名也始终作为单个名称显示。如果组名以单个名称表示，则组名字段始终为空。名称可以属于组，但不是必需的。我举个例子：

Name    Group    Value
-----------------------
name1 | group1 | 10,000
name2 | group1 | 12,000
name3 | group2 |  9,000
group1|        | 40,000
name4 | group2 | 30,000
name5 |        | 11,000
group2|        |  1,000
name6 | group1 | 19,000

对于每个单独的名称，我希望从该表中得到以下结果：

• 如果名称属于组，则取名称、组名称和组的最大值
• 如果名称不属于某个组，则将name、name再次作为组名和值

根据上述示例，结果如下所示：

Name    Group    Max(V)
-----------------------
name1 | group1 | 40,000
name2 | group1 | 40,000
name3 | group2 | 30,000
group1| group1 | 40,000
name4 | group2 | 30,000
name5 | name5  | 11,000
group2| group2 | 30,000
name6 | group1 | 40,000

我知道如何通过两个单独的查询和一些python数据混合来获得它。然而，我想知道哪一种是最python的方法来实现它，如果有可能得到相同的结果与一个单一的查询

Answer 1

.headers on
.mode column
.width 1 8 8 8

with
data as (
  select 1 n,'name1'  name, 'group1' groupcol, 10000 v union
  select 2 n,'name2'  name, 'group1' groupcol, 12000 v union
  select 3 n,'name3'  name, 'group2' groupcol,  9000 v union
  select 4 n,'group1' name, null     groupcol, 40000 v union
  select 5 n,'name4'  name, 'group2' groupcol, 30000 v union
  select 6 n,'name5'  name, null     groupcol, 11000 v union
  select 7 n,'group2' name, null     groupcol,  1000 v union
  select 8 n,'name6'  name, 'group1' groupcol, 19000 v
),
data2 as (select ifnull(groupcol,name) groupcol,* from data)
select
  n,
  name,
  groupcol,
  (select max(v) from data2 where groupcol=d.groupcol) maxv
from data2 d
order by n;

结果：

n  name      groupnum  maxv    
-                  
1  name1     group1    40000   
2  name2     group1    40000   
3  name3     group2    30000   
4  group1    group1    40000   
5  name4     group2    30000   
6  name5     name5     11000   
7  group2    group2    30000   
8  name6     group1    40000   

您需要SQLite版本3.8.3（2014）或更新版本才能理解WITH子句。如果您使用的是较旧的版本，那么sql很容易重写，只需在后面使用带子选择的圆括号即可

Answer 2

最常用的SQL方法是使用单个SQL查询

可以使用CASE expression和correlated subquery计算两个不同的值：

SELECT Name,
       CASE WHEN GroupColumn IS NOT NULL
            THEN GroupColumn
            ELSE Name
       END AS "Group",
       (SELECT max(Value)
        FROM MyTable AS T2
        WHERE T2.GroupColumn = MyTable.GroupColumn
           OR T2.Name        = MyTable.GroupColumn
       ) AS "Max(V)"
FROM MyTable;

（但是有一个helper function来替换空值；GroupColumn的整个表达式可以简化为ifnull(GroupColumn, Name)。）