【Django】Django 如何实现 如下 联表 JOIN 查询？

SQL语句：

select distinct a.device_hash, sum(b.cmn_merge_count) from 
(select distinct device_hash from tbl_fileprotect_svc_instance where customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e') 
as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash group by a.device_hash;

简化SQL如下（把子查询转化为WHERE语句）：

select distinct a.device_hash, sum(b.cmn_merge_count) from 
tbl_fileprotect_svc_instance
as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash 
where a.customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e'
group by a.device_hash;



select distinct a.device_hash, sum(b.cmn_merge_count) from  tbl_fileprotect_svc_instance as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash where a.customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e' group by a.device_hash;

select distinct a.device_hash, sum(b.cmn_merge_count) as alarm_sum from  tbl_fileprotect_svc_instance as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash where a.customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e' group by a.device_hash;

 

Django代码：

TODO：
方法一：

ret = []
device_list = models.FILE_PROTECT_INSTANCE.objects.filter(customer_id=customer_id)
for item in device_list:
    tmp_dict = {}
    tmp_dict['device_hash'] = item.device_hash
    tmp_dict['hostname'] = item.hostname
    tmp_dict['status'] = item.status 
    from django.db.models import Sum
    alarm_sum_group_items = models.FILE_PROTECT_ALARM.objects.filter(customer_id=customer_id).filter(device_hash=tmp_dict['device_hash']).values('device_hash').annotate(alarm_sum=Sum('cmn_merge_count'))
    #tmp_dict['customer_id'] = customer_id
    tmp_dict['alarm_sum'] = alarm_sum_group_items[0]['alarm_sum'] if (len(alarm_sum_group_items)!=0) else 0
    ret.append(tmp_dict)

 

方法二：

hashes = A.objects.values_list("device_hash", flat=True).filter(customer_id="3f500ac5-020d-3ce3-a2a2-51a59ddd606e")

B.objects.filter(device_hash__in=hashes).values_list("device_hash").annotate(Sum("cmn_merge_count"))

 

SQL实现聚合查询统计（Sum，Count等）

from django.db.models import Sum
        #start_time = datetime.utcfromtimestamp(data['start_time']).replace(tzinfo=utc)
        #end_time = datetime.utcfromtimestamp(data['end_time']).replace(tzinfo=utc)
        end_time = datetime.now()
        start_time = end_time + timedelta(days=-1)
        condition['cmn_alert_time__range'] = (start_time, end_time)
        #alarm_sum_group_items_day = models.FILE_PROTECT_ALARM.objects.filter(**condition).values('customer_id').annotate(alarm_sum_day=Sum('cmn_merge_count'))
        alarm_sum_day = models.FILE_PROTECT_ALARM.objects.filter(**condition).aggregate(Sum("cmn_merge_count"))
        day_sum = alarm_sum_day['cmn_merge_count__sum'] if (alarm_sum_day['cmn_merge_count__sum'] is not None) else 0 
        
        end_time = datetime.now()
        start_time = end_time + timedelta(days=-7)
        condition['cmn_alert_time__range'] = (start_time, end_time)
        #alarm_sum_group_items_week = models.FILE_PROTECT_ALARM.objects.filter(**condition).values('customer_id').annotate(alarm_sum_week=Sum('cmn_merge_count'))
        alarm_sum_week = models.FILE_PROTECT_ALARM.objects.filter(**condition).aggregate(Sum("cmn_merge_count"))
        week_sum = alarm_sum_week['cmn_merge_count__sum'] if (alarm_sum_week['cmn_merge_count__sum'] is not None) else 0 
        
        condition = {}
        #condition['device_hash'] = data['device_hash']
        condition['customer_id'] = customer_id
        end_time = datetime.now()
        #start_time = end_time + timedelta(days=-7)
        condition['cmn_alert_time__lte'] = end_time
        #alarm_sum_group_items_all = models.FILE_PROTECT_ALARM.objects.filter(**condition).values('customer_id').annotate(alarm_sum_all=Sum('cmn_merge_count'))
        alarm_sum_all = models.FILE_PROTECT_ALARM.objects.filter(**condition).aggregate(Sum("cmn_merge_count"))
        all_sum = alarm_sum_all['cmn_merge_count__sum'] if (alarm_sum_all['cmn_merge_count__sum'] is not None) else 0 

参考资料：
http://stackoverflow.com/questions/6481279/django-sum-query

 

 

参考资料：

DINSTINCT： http://www.w3school.com.cn/sql/sql_distinct.asp

RAW语句实现：http://yeelone.blog.51cto.com/1476571/946694

http://www.it165.net/database/html/201207/2755.html

http://stackoverflow.com/questions/21355601/django-orm-inner-join

http://djangobook.py3k.cn/2.0/chapter10/

http://blog.csdn.net/clh604/article/details/9789839

https://www.douban.com/note/301166150/