我使用的是客户模型
class Customer(models.Model):
id = models.AutoField(primary_key=True)
email = models.EmailField()
For eg: table has following data:
id Email
101 a@abc.com
102 b@abc.com
103 c@abc.com
104 a@abc.com
105 b@abs.com
106 b@abc.com
107 d@abc.com
108 d@abc.com
I want following Output:
number of times number of emails
1 1 (c@abc.com)
2 2 (d@abc.com, a@abc.com)
3 1 (b@abc.com)
我想查询邮件重复了多少次:
我尝试了以下查询Customer.objects.all () . values(电子邮件).annotate(=总数('邮件')).order_by(总)它给出以下输出
[
{
'total':1,
'email':u'cgaujjlitU@example.com'
},
{
'total':1,
'email':u'fgh@dfg.com'
},
{
'total':1,
'email':u'jay@dfg.com'
},
{
'total':1,
'email':u'test3@gmail.com'
},
{
'total':1,
'email':u'tester19@fgh.com'
},
{
'total':2,
'email':u'carlos+bj1@fgh.io'
},
{
'total':17,
'email':u'dfe@sedd.com'
}
]
aggregated_list = Customer.objects.all().values('email').annotate(total=Count('email')).order_by('total')
email_occurance = {}
for item in aggregated_list:
get_total = item['total']
if get_total not in email_occurance:
email_occurance[get_total] = (item['email'],)
else:
existing_email_tuple = email_occurance[get_total]
existing_email_list = list(existing_email_tuple)
existing_email_list.append(item['email'])
new_email_tuple = tuple(existing_email_list)
email_occurance[get_total] = new_email_tuple
将给你:
email_occurance= {
1: ('cgaujjlitU@example.com',
'fgh@dfg.com',
'jay@dfg.com',
'test3@gmail.com',
'tester19@fgh.com'
),
2: ('carlos+bj1@fgh.io',),
17: ('dfe@sedd.com',)
}
这是您需要的输出吗?
您需要进入原始sql的世界,但并非所有数据库都支持它。在postgresql中,你需要查看array_agg,而在mysql和sqlite中,使用的函数是group_concat