1. updated 2019-01-03 多primary key

1. 起因
   我这边要在搭配系统里做一个看板功能, 大概就是统计每一个搭配师的周/月/总工作量, 盒/件转化率, 搭配的延时率, 过快率, 用户各项反馈的满意率, 搭配师回看用户反馈的回看率, 个人统计, 小组统计等等...
   为了统计这些数据我这边需要一个每日的定时脚本, 从记录的行为日志里分析出搭配师的操作流程单独存一张表. 再根据这张表聚合出周数据/月数据, 并维护一个视图计算一些总和相关的指标.
   行为记录表起初如下:

class StylistBehaviorBackup(models.Model):
    pid = models.IntegerField(primary_key=True)
    stylist_id = models.IntegerField(primary_key=True)
    stylist_name = models.CharField(max_length=100, blank=True, null=True)
    reviewer_name = models.CharField("审核人名字", max_length=100, null=True, blank=True)

    get_look_time = models.DateTimeField("分配订单时间", null=True, blank=True)

    first_press_start_time = models.DateTimeField("第一次按下‘开始搭配’按钮时间", null=True, blank=True)
    first_styling_start_time = models.DateTimeField("加入第一件商品前的那次按下'开始搭配'时间", null=True, blank=True)
    styling_signal = models.IntegerField("开始搭配信号", default=0)  # 0-未搭配；1-已开始搭配
    first_styling_submit_time = models.DateTimeField("后端shipment_create时间", null=True, blank=True)
    first_styling_duration = models.IntegerField("第一次搭配时长（秒）", null=True, blank=True)
    first_review_start_time = models.DateTimeField("复核开始时间", null=True, blank=True)
    first_review_sendback_time = models.DateTimeField("第一次打回时间", null=True, blank=True)
    first_review_pass_time = models.DateTimeField("第一次通过时间", null=True, blank=True)
    first_review_duration = models.IntegerField("第一次复核时长（秒）", null=True, blank=True)

    second_styling_start_time = models.DateTimeField(null=True, blank=True)
    second_styling_submit_time = models.DateTimeField(null=True, blank=True)
    second_styling_duration = models.IntegerField(null=True, blank=True)
    second_review_start_time = models.DateTimeField(null=True, blank=True)
    second_review_sendback_time = models.DateTimeField(null=True, blank=True)
    second_review_pass_time = models.DateTimeField(null=True, blank=True)
    second_review_duration = models.IntegerField(null=True, blank=True)

    warehouse_sendback_time = models.DateTimeField("第一次仓库打回时间", null=True, blank=True)
    wait_in_time = models.DateTimeField("第一次放入'暂不服务'时间", null=True, blank=True)
    wait_out_time = models.DateTimeField(null=True, blank=True)
    first_click_feedback_time = models.DateTimeField("第一次查看'已反馈'时间", null=True, blank=True)

    first_process_time = models.DateTimeField("初次审核通过", null=True, blank=True)

    created = models.DateTimeField(null=True, blank=True, auto_now_add=True)
    updated = models.DateTimeField(null=True, blank=True, auto_now=True)

    def __str__(self):
        return f"{self.pid}: {self.stylist_id} {self.stylist_name}"

    class Meta:
        managed = False
        db_table = 'stylist_behavior_backup'
        app_label = "bi"

周聚合sql

# count 只有在遇到null时不计数
select
  DATE(
      DATE_SUB(
          first_process_time,
          INTERVAL DAYOFWEEK(DATE_SUB(first_process_time, INTERVAL 1 DAY)) - 1 DAY
      )
  ) AS time,
#   DATE(DATE_SUB(first_process_time, INTERVAL DAYOFMONTH(first_process_time) - 1 DAY)) AS time,
  stylist_id, a.username, count(shipment.id) AS box_amount, count(items_sold>0 or NULL ) AS box_sold_amount,
  sum(k.all_co) AS "总搭配件数", sum(k.sold_co) AS '卖出件数', count(style_star_review !='' OR NULL ) AS '总反馈盒数',
  sum(style_star_review) AS "总搭配满意度", sum(like_design_co) AS '款式喜欢件数', sum(design_co) AS '款式反馈件数',
  sum(fit_size_co) AS "尺码合适件数", sum(size_co) AS '尺码反馈件数',
  count(first_process_time>=shipment.expected_date OR NULL ) AS '搭配延时数',
  count(items_sold>0 or NULL) / count(shipment.id) AS "盒购买率",
  sum(k.sold_co) / sum(k.all_co) AS "件转化率",
  sum(like_design_co) / sum(design_co) AS "款式满意度",
  sum(fit_size_co) / sum(size_co) AS "尺码准确率",
  sum(style_star_review) / count(style_star_review !='' OR NULL ) AS "搭配评分"
FROM shipment
  LEFT JOIN auth_user a ON shipment.stylist_id = a.id
  LEFT JOIN (
    select shipment_id, count(si.id) AS 'all_co', count(si.sold=1 or NULL) AS 'sold_co',
  count(si.design_feedback in ('有点喜欢', '喜欢', '非常喜欢') or NULL ) AS 'like_design_co',
  count(si.design_feedback != '' or NULL ) AS 'design_co',
  count(si.size_feedback = '正好' or NULL ) AS 'fit_size_co',
  count(si.size_feedback != '' or NULL ) AS 'size_co'
    from shipmentitem AS si
      LEFT JOIN shipment s ON si.shipment_id = s.id WHERE si.status = '100'
    GROUP BY shipment_id) k ON shipment.id = k.shipment_id
WHERE first_process_time IS NOT NULL GROUP BY time, stylist_id;

问题就发生在产品突然发现搭配师周记录的反馈阅读率超过了100%
震惊

2. 发现原因
   发现原因的过程其实还挺漫长的... 因为没想到造成的原因是记录搭配师的id和name有部分数据不一致.
   几个脚本的代码中明面上涉及到这个id和name的只有一处, 还是实时获取的.
   当然最后还发现了一处暗中修改的.
3. debug
   事实上就是这处暗中修改导致的.

    def migrate_date_from_prod(self):
        sbs = StylistBehaviorBackup.objects.all()
        print(f"Total is {sbs.count()}")
        for index, sb in enumerate(sbs):
            if index % 500 == 0:
                print(f"Now is {index}...")
            sm = Shipment.objects.using("prod").filter(pid=sb.pid, stylist_id=sb.stylist_id).first()
            if not sm or not sm.first_process_time:
                continue
            sb.first_process_time = sm.first_process_time
            sb.save()

看起来毫不相干的一段代码. 是在最后没办法了一步一步print数据时发现的, 在跑完这段赋值代码后stylist的id和name对应关系就变化了.
先说个前提, 因为一个订单是可能由好几个搭配师经手, 所以这个行为分析表里是可能存在一个订单号有不同的搭配师, 每一种分别是一条记录.
不过我这边表是双主键的存储, pid和stylist_id共同主键, 所以我本来觉得也没什么问题.
然而后来问题就出在这了, django的orm不支持双主键
这里额外说一下发现途径:
django可以打印出最近通过orm操作sql的实际执行代码:
https://docs.djangoproject.com/en/dev/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running

from django.db import connections
connections["bi"].queries

然后发现最后的where其实只取了一个主键

4. 解决办法
   改表结构了..
   加个id做主键, 原来的pid和stylist_id用unique约束

Meta中加入
        unique_together = (("pid", "stylist_id"),)