Django查询以获取重复多少次的电子邮件

Question

我用的是客户模型

    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（'email'）.annotate（total=Count（'email'））.order_by（'total'） 它给出了以下输出

   [  
       {  
          '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'
       }
    ]

Answer 1

您需要进入原始sql的世界，但并非所有数据库都支持它。对于postgresql，您需要查看array_agg，而在mysql和sqlite中，要使用的函数是group_concat

Answer 2

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',)
    }

这是你需要的输出吗？你知道吗