Django ORM实现按天获取数据去重求和

def total_data(request):
    data = request_body(request, 'POST')
    if not data:
        return http_return(400, '参数错误')
    # 前端传入毫秒为单位的时间戳
    startTimestamp = data.get('startTime', '')
    endTimestamp = data.get('endTime', '')

    if startTimestamp and endTimestamp:
        startTimestamp = int(startTimestamp/1000)
        endTimestamp = int(endTimestamp/1000)
    else:
        return http_return(400, '参数有误')
    # 小于2019-05-30 00:00:00的时间不合法
    if endTimestamp < startTimestamp or endTimestamp <= 1559145600 or startTimestamp <= 1559145600:
        return http_return(400, '无效时间')
    if startTimestamp and endTimestamp:
        # 给定时间查询
        startTime = datetime.fromtimestamp(startTimestamp)
        endTime = datetime.fromtimestamp(endTimestamp)
        t1 = datetime(startTime.year, startTime.month, startTime.day)
        t2 = datetime(endTime.year, endTime.month, endTime.day, 23, 59, 59, 999999)
        # 用户总人数
        totalUsers = User.objects.exclude(status='destroy').count()
        # 音频总数
        totalAudioStory = AudioStory.objects.filter(isDelete=False).count()
        # 专辑总数
        totalAlbums = Album.objects.filter(isDelete=False).count()
        # 新增用户人数
        newUsers = User.objects.filter(createTime__range=(t1, t2)).exclude(status='destroy').count()
        # 活跃用户人数
        activityUsers = LoginLog.objects.filter(createTime__range=(t1, t2), isManager=False).values('userUuid_id').\
            annotate(Count('userUuid_id')).count()
        # 新增音频数
        newAudioStory = AudioStory.objects.filter(createTime__range=(t1, t2)).count()

        # 男性
        male = User.objects.filter(gender=1).exclude(status='destroy').count()

        # 女性
        female = User.objects.filter(gender=2).exclude(status='destroy').count()

        # 未知
        unkonwGender = User.objects.filter(gender=0).exclude(status='destroy').count()


        # 模板音频
        aduioStoryCount = AudioStory.objects.filter(
            isDelete=False, audioStoryType=1, isUpload=1, createTime__range=(t1, t2)).count()

        # 自由录制
        freedomStoryCount = AudioStory.objects.filter(
            isDelete=False, audioStoryType=0, isUpload=1, createTime__range=(t1, t2)).count()


        # 儿歌
        tags1 = Tag.objects.filter(code="RECORDTYPE", name='儿歌').first()
        tags1Count =  tags1.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).count()     # 儿歌作品数
        user1Count =  tags1.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).\
            values('userUuid_id').annotate(Count('userUuid_id')).count()                                #  录音类型人数，去重

        # result = Tag.objects.filter(code="RECORDTYPE").annotate(Count('tagsAudioStory'))

        # 父母学堂
        tags2 = Tag.objects.filter(code="RECORDTYPE", name='父母学堂').first()
        tags2Count = tags2.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).count()
        user2Count = tags2.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).\
            values('userUuid_id').annotate(Count('userUuid_id')).count()

        # 国学
        tags3 = Tag.objects.filter(code="RECORDTYPE", name='国学').first()
        tags3Count = tags3.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).count()
        user3Count = tags3.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).\
            values('userUuid_id').annotate(Count('userUuid_id')).count()

        # 英文
        tags4 = Tag.objects.filter(code="RECORDTYPE", name='英文').first()
        tags4Count = tags4.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).count()
        user4Count = tags4.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)). \
            values('userUuid_id').annotate(Count('userUuid_id')).count()

        # 其他
        tags5 = Tag.objects.filter(code="RECORDTYPE", name='其他').first()
        tags5Count = tags5.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).count()
        user5Count = tags5.tagsAudioStory.filter(isDelete=False, createTime__range=(t1, t2)).\
            values('userUuid_id').annotate(Count('userUuid_id')).count()

        recordTypePercentage = [
            {'name': '儿歌', 'tagsNum': tags1Count, 'userNum': user1Count},
            {'name': '儿歌', 'tagsNum': tags2Count, 'userNum': user2Count},
            {'name': '国学', 'tagsNum': tags3Count, 'userNum': user3Count},
            {'name': '英文', 'tagsNum': tags4Count, 'userNum': user4Count},
            {'name': '其他', 'tagsNum': tags5Count, 'userNum': user5Count}
        ]


        # 活跃用户排行
        data1_list = []
        # result = AudioStory.objects.filter(isDelete=False, createTime__range=(t1, t2)).values('userUuid_id').annotate(Count('userUuid_id'))[:1]
        res = User.objects.annotate(audioStory_count_by_user = Count("useAudioUuid")).order_by('-audioStory_count_by_user')[:5]
        for index,item in enumerate(res.values()):
            data = {
                'orderNum': index+1,
                'name': item['nickName'],
                'recordCount': item['audioStory_count_by_user']
            }
            data1_list.append(data)
        # 热门录制排行
        data2_list = []
        res = Story.objects.filter(status="normal", createTime__range=(t1, t2)).order_by('-recordNum')[:5]
        for index,item in enumerate(res.values()):
            data = {
                'orderNum': index + 1 or -1,
                'name': item['name'] or '',
                'recordNum': item['recordNum'] or 0
            }
            data2_list.append(data)

        # 热门播放排行
        data3_list = []
        audioStory = AudioStory.objects.filter(isDelete=False, createTime__range=(t1, t2)).order_by('-playTimes')[:5]
        for index,item in enumerate(audioStory):
            data = {
                'orderNum': index + 1,
                'name': item.storyUuid.name if item.audioStoryType else item.name,
                'playTimes': item.playTimes
            }
            data3_list.append(data)

        # 图表数据--新增用户
        graph1 = User.objects.filter(createTime__range=(t1, t2)).\
            extra(select={"time": "DATE_FORMAT(createTime,'%%Y-%%m-%%e')"}).\
            order_by('time').values('time')\
            .annotate(userNum=Count('createTime')).values('time', 'userNum')
        if graph1:
            graph1 = list(graph1)
        else:
            graph1 = []

        # 活跃用户
        graph2 = LoginLog.objects.filter(createTime__range=(t1, t2), isManager=False). \
            extra(select={"time": "DATE_FORMAT(createTime,'%%Y-%%m-%%e')"}). \
            values('time').annotate(userNum=Count('createTime', distinct=True)).values('time', 'userNum')
        if graph2:
            graph2 = list(graph2)
        else:
            graph2 = []


        return http_return(200, 'OK',
                           {
                               'totalUsers': totalUsers,            # 总用户人数
                               'totalAudioStory': totalAudioStory,  # 音频总数
                               'totalAlbums': totalAlbums,          # 总的专辑数
                               'newUsers': newUsers,                # 新增用户人数
                               'activityUsers': activityUsers,      # 活跃用户人数
                               'newAudioStory': newAudioStory,      # 新增音频数
                               'activityUsersRank': data1_list,     # 活跃用户排行
                               'male': male,                         # 男性
                               'female': female,                     # 女性
                               'unkonwGender': unkonwGender,        # 未知性别
                               'aduioStoryCount': aduioStoryCount,  # 模板音频数量
                               'freedomStoryCount': freedomStoryCount,  # 自由录制音频数量
                               'recordTypePercentage': recordTypePercentage,
                               'hotRecordRank': data2_list,         # 热门录制排行
                               'hotPlayAudioStoryRank': data3_list,     # 热门播放排行
                               'newUserGraph': graph1,              # 新增用户折线图
                               'activityUserGraph': graph2,         # 活跃用户折线图
                           })