时间戳

首页 > 解决方案 > SQL / Python - 如何从另一个表返回每个属性和子属性的计数

python - SQL / Python - 如何从另一个表返回每个属性和子属性的计数

问题描述

我有 SELECT 返回表，该表具有：

区域的 -5 个可能值（从 1 到 5）和

-3 个可能的年龄值(1-3)，每个年龄组有 2 个可能的性别值（1 或 2）。

所以表 1. 看起来像这样：

+----------+-----------+--------------+---------------+---------+
| att_name | att_value | sub_att_name | sub_att_value | percent |
+----------+-----------+--------------+---------------+---------+
| region   |         1 | NULL         |             0 |      34 |
| region   |         2 | NULL         |             0 |      22 |
| region   |         3 | NULL         |             0 |      15 |
| region   |         4 | NULL         |             0 |      37 |
| region   |         5 | NULL         |             0 |      12 |
| age      |         1 | gender       |             1 |      28 |
| age      |         1 | gender       |             2 |       8 |
| age      |         2 | gender       |             1 |      13 |
| age      |         2 | gender       |             2 |      45 |
| age      |         3 | gender       |             1 |      34 |
| age      |         3 | gender       |             2 |      34 |
+----------+-----------+--------------+---------------+---------+

第二个表包含来自表 1 的值的记录。其中表 1.att_name和sub_att_name是表 2. 的唯一值。属性：

+--------+-----+-----+
| region | age | gen |
+--------+-----+-----+
|      2 |   2 |   1 |
|      3 |   1 |   2 |
|      3 |   3 |   2 |
|      1 |   3 |   1 |
|      4 |   2 |   2 |
|      5 |   2 |   1 |
+--------+-----+-----+

我想从第二个表中返回区域和年龄/性别属性的每个唯一值的计数。

最终结果应如下所示：

+----------+-----------+-----------------+--------------+---------------+---------------------+---------+
| att_name | att_value | att_value_count | sub_att_name | sub_att_value | sub_att_value_count | percent |
+----------+-----------+-----------------+--------------+---------------+---------------------+---------+
| region   |         1 | 1               | NULL         |             0 | NULL                |      34 |
| region   |         2 | 1               | NULL         |             0 | NULL                |      22 |
| region   |         3 | 2               | NULL         |             0 | NULL                |      15 |
| region   |         4 | 1               | NULL         |             0 | NULL                |      37 |
| region   |         5 | 1               | NULL         |             0 | NULL                |      12 |
| age      |         1 | NULL            | gender       |             1 | 0                   |      28 |
| age      |         1 | NULL            | gender       |             2 | 1                   |       8 |
| age      |         2 | NULL            | gender       |             1 | 2                   |      13 |
| age      |         2 | NULL            | gender       |             2 | 1                   |      45 |
| age      |         3 | NULL            | gender       |             1 | 1                   |      34 |
| age      |         3 | NULL            | gender       |             2 | 1                   |      34 |
+----------+-----------+-----------------+--------------+---------------+---------------------+---------+

解释

区域- 因此没有子属性sub_att_name并且sub_att_value_count为 NULL。 att_value_count- 计算每个独特区域的出现次数（除区域 3 显示 2 次外，所有区域均为 1）。

年龄/性别- 计算年龄和性别的外观组合（组为 1/1、1/2、2/1、2/2 和 3/1、3/2）。由于我们只需要为组合填写值，att_value_count因此为 NULL。

我在这个问题中标记了 python 和 pandas，因为我根本不知道这在 SQL 中是否可行……我希望这是因为我们正在使用分析工具更自然地从数据库中提取表和视图。

编辑

SQL - 答案看起来很复杂，明天我会测试它是否有效。

Python - 现在似乎更有吸引力 - 有没有办法解析att_name和sub_att_name，找到 1 级和 2 级属性并采取相应的行动？我认为这只有在 python 中才有可能，而且我们确实有不同的属性和属性级别。我已经感谢给出的答案！

标签： pythonmysqlsqlpandasjoin

解决方案

这是一个熊猫解决方案，基本上，lookup或map.

df['att_value_count'] = np.nan
s = df['att_name'].eq('region')

df.loc[s, 'att_value_count'] = df.loc[s,'att_value'].map(df2['region'].value_counts())

# step 2
counts = df2.groupby('age')['gen'].value_counts().unstack('gen', fill_value=0)

df['sub_att_value_count'] = np.nan
tmp = df.loc[~s, ['att_value','sub_att_value']]
counts = df2.groupby('age')['gen'].value_counts().unstack('gen', fill_value=0)

df.loc[~s, 'sub_att_value_count'] = counts.lookup(tmp['att_value'], tmp['sub_att_value'])

您也可以使用merge它，因为它对 SQL 更友好。例如，在步骤 2 中：

counts = df2.groupby('age')['gen'].value_counts().reset_index(name='sub_att_value_count')

(df.merge(counts, 
         left_on=['att_value','sub_att_value'],
         right_on=['age','gen'],
         how = 'outer'
        ) 
   .drop(['age','gen'], axis=1)
)

输出：

    att_name      att_value  sub_att_name      sub_att_value    percent    att_value_count    sub_att_value_count
--  ----------  -----------  --------------  ---------------  ---------  -----------------  ---------------------
 0  region                1  nan                           0         34                  1                    nan
 1  region                2  nan                           0         22                  1                    nan
 2  region                3  nan                           0         15                  2                    nan
 3  region                4  nan                           0         37                  1                    nan
 4  region                5  nan                           0         12                  1                    nan
 5  age                   1  gender                        1         28                nan                      0
 6  age                   1  gender                        2          8                nan                      1
 7  age                   2  gender                        1         13                nan                      2
 8  age                   2  gender                        2         45                nan                      1
 9  age                   3  gender                        1         34                nan                      1
10  age                   3  gender                        2         34                nan                      1

更新：如果这没有运行，请原谅我的 SQL 技能（它应该运行）

select 
  b.*
  c.sub_att_value_count
from 
(select 
  df1.*
  a.att_value_count
from
  (select 
    region, count(*) as att_value_count
   from df2
   group by region
  ) as a
  full outer join df1
  where df1.att_value = a.region
) as b
full outer join 
(
    select 
      age, gender, count(*) as sub_att_value_count
    from df2
    group by age, gender
) as c
where b.att_value = c.age and b.sub_att_value = c.gender

推荐阅读