WITH temp AS(
      SELECT
              dc.date_time,
              dc.user_id,
              COUNT(dc.user_id) AS matches,
              COUNT(d.user_id) AS active_matches, -- 在配对当天有活跃的other_user
              COUNT(dc.user_id) FILTER(WHERE cu.gender = 'male') AS male_matches,
              COUNT(dc.user_id) FILTER(WHERE cu.gender = 'female') AS female_matches,

              (COALESCE(COUNT(dc.user_id) FILTER(WHERE cu.gender = 'female'))::numeric / NULLIF(COUNT(dc.user_id)::numeric, 0))::numeric(32, 4) AS ratio_female_matches,

              SUM(COALESCE(dm.messages_count,0) + COALESCE(dm.audios,0) + COALESCE(dm.pictures,0)) AS messages,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count > 0 OR dm.audios > 0 OR dm.pictures > 0),0) AS users,  -- 对多少当天配对用户发了消息，对话的数量（对面回不回不清楚）
              COALESCE(COUNT(*) FILTER(WHERE (dm.messages_count = 0 AND dm1.messages_count > 0) OR (dm.messages_count > 0 AND dm1.messages_count = 0)),0) AS onesided, -- 在当天配对的用户两人之中只有一方有消息，我发你没回或者你发我没回
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 1 AND dm1.messages_count >= 1),0) AS mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 2 AND dm1.messages_count >= 2),0) AS two_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 5 AND dm1.messages_count >= 5),0) AS five_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 8 AND dm1.messages_count >= 8),0) AS eight_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 10 AND dm1.messages_count >= 10),0) AS ten_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 15 AND dm1.messages_count >= 15),0) AS fifteen_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 20 AND dm1.messages_count >= 20),0) AS twenty_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 30 AND dm1.messages_count >= 30),0) AS thirty_mmsg_conversation,
              COALESCE(COUNT(*) FILTER(WHERE dm.messages_count >= 50 AND dm1.messages_count >= 50),0) AS fifty_mmsg_conversation
      FROM
              daily_conversations_by_users dc
      LEFT JOIN
              core_users cu ON(dc.other_user_id = cu.user_id)
      LEFT JOIN
              daily_messages_by_users dm ON(dc.user_id = dm.user_id AND dc.other_user_id = dm.other_user_id AND dc.date_time = dm.date_time)
      LEFT JOIN
              daily_messages_by_users dm1 ON(dc.user_id = dm1.other_user_id AND dc.other_user_id = dm1.user_id AND dm.date_time = dm1.date_time)
      LEFT JOIN
              dailyuseractivities d ON(dc.other_user_id = d.user_id AND dc.date_time = d.date_time)
      WHERE
              --dc.date_time > COALESCE(_last_run.max_date_time, '2014-01-01')
              dc.date_time = '2017-11-15'
      GROUP BY
              dc.date_time, dc.user_id
      ORDER BY
              dc.date_time, dc.user_id
)
UPDATE stats.daily_matches_by_users_func z   -- 表的schema名称需要修改
SET
        active_matches = t.active_matches,
        male_matches = t.male_matches,
        female_matches = t.female_matches,
        ratio_female_matches = t.ratio_female_matches,
        messages = t.messages,
        users = t.users,
        onesided = t.onesided,
        mmsg_conversation = t.mmsg_conversation,
        two_mmsg_conversation = t.two_mmsg_conversation,
        five_mmsg_conversation = t.five_mmsg_conversation,
        eight_mmsg_conversation = t.eight_mmsg_conversation,
        ten_mmsg_conversation = t.ten_mmsg_conversation,
        fifteen_mmsg_conversation = t.fifteen_mmsg_conversation,
        twenty_mmsg_conversation = t.twenty_mmsg_conversation,
        thirty_mmsg_conversation = t.thirty_mmsg_conversation,
        fifty_mmsg_conversation = t.fifty_mmsg_conversation
FROM
        temp t
WHERE
        z.user_id = t.user_id
AND z.date_time = t.date_time
;