python - SQLite - 查询返回相关记录

Question

我有一个数据库，其中包含具有上述关系的下表。我想获得每个客户给出的每个产品的平均分数，如所需的输出所示。

“grp”

g_id    g_name
1       Ag.f
2       Gh.h

“customers”，其中 c_g_id 引用“grp”中的 g_id

c_id    c_name    g_id
"1"     "DA"      "1"
"2"     "AŠ"      "1"
"3"     "EK"      "1"
"4"     "PK"      "1"
"5"     "DD"      "2"
"6"     "AA"      "2"
"7"     "EE"      "2"

“产品”，其中 g_id 引用“grp”中的 g_id

m_id    sequence_no  g_id   name

"1"     "1"          "1"    "product1"
"2"     "2"          "1"    "product2"
"3"     "3"          "1"    "product3"
"4"     "1"          "2"    "p1"
"5"     "2"          "2"    "p2"

“分数”

c_id 引用“客户”中的 c_idm_id 引用“产品”中的 m_id

score_id    customer_name   c_id    m_id    score

"1"         "DA"            "1"     "1"     "2" 
"2"         "DA"            "1"     "2"     "2" 
"3"         "DA"            "1"     "3"     "2" 
"4"         "AŠ"            "2"     "1"     "3" 
"5"         "AŠ"            "2"     "2"     "2" 
"6"         "AŠ"            "2"     "3"     "3" 
"7"         "EK"            "3"     "1"     "2" 
"8"         "EK"            "3"     "2"     "3" 
"9"         "EK"            "3"     "3"     "1" 
"10"        "PK"            "4"     "1"     "2" 
"11"        "PK"            "4"     "2"     "3" 
"12"        "PK"            "4"     "3"     "1" 
"13"        "DD"            "5"     "4"     "2" 
"14"        "DD"            "5"     "5"     "2" 
"15"        "AA"            "6"     "4"     "3" 
"16"        "AA"            "6"     "5"     "2" 
"17"        "EE"            "7"     "4"     "2" 
"18"        "EE"            "7"     "5"     "3"

期望的输出:

sequence_no g_id    name        avg.score
1           1       "product1"  (2+3+2+2)/4
2           1       "product2"  (2+2+3+3)/4
3           1       "product3"  (2+3+1+1)/4

Answer 1

您可以使用 avg 函数和分组依据

select  products.name, products.g_id, avg(score)
from scores
inner join products on products.m_id = scores.m_id
group by products.name, products.g_id

如果您需要一些相关列，最终您可以加入其他表