如何获得独特的用户参与度？ [英] How do I get unique user engagements?

问题描述

我正在尝试使用 engagement_type

我以前有以下查询。

SELECT u.id, u.fname, u.lname FROM (
  SELECT id, engagement_type FROM (
    SELECT user_id AS id, 'comment'
    FROM comments WHERE commentable_id = 48136 AND commentable_type = 'Video'
    UNION ALL
    SELECT user_id AS id, 'like'
    FROM likes WHERE likeable_id = 48136 AND likeable_type = 'Video'
  ) AS a
  GROUP BY id
  LIMIT 10
) b JOIN users u USING (id);

返回值：

id        | fname      | lname
------------------------------
1         | joe        | abc
2         | sarah      | qer
3         | megan      | tryey
4         | john       | vdfa

哪个很好。现在，我要包含参与度类型。我想出了这个：

Which is fine. Now, I want to include the engagment type. I've come up with this:

SELECT u.id, u.fname, u.lname, engagement_type FROM (
  SELECT id, engagement_type FROM (
    SELECT user_id AS id, 'comment' AS engagement_type
    FROM comments WHERE commentable_id = 48136 AND commentable_type = 'Video'
    UNION ALL
    SELECT user_id AS id, 'like' AS engagement_type FROM likes
    WHERE likeable_id = 48136 AND likeable_type = 'Video'
  ) AS a
  GROUP BY id, engagement_type
  LIMIT 10
) b JOIN users u USING (id);

现在返回：

id        | fname      | lname    | engagement_type
---------------------------------------------------
1         | joe        | abc      | comment
2         | sarah      | qer      | like
3         | megan      | tryey    | like
4         | john       | vdfa     | like
1         | joe        | abc      | like     
3         | megan      | tryey    | comment

上述唯一的问题。结果不再是唯一的。如您所见，Joe和Megan有2个条目。

The only problem with above. The results are not unique anymore. As you can see, Joe and Megan have 2 entries.

有什么主意，我想让它起作用吗？

Any idea how I can get this to work?

推荐答案

在PostgreSQL中使用 DISTINCT ON 简单。

由于缺乏定义，我选择了第一个 engagement_type 根据其排序顺序：

Simpler with DISTINCT ON in PostgreSQL.
For lack of definition I pick the first engagement_type according to its sort order:

SELECT u.id, u.fname, u.lname, b.engagement_type
FROM  (
   SELECT DISTINCT ON (1)
          id, engagement_type
   FROM (
      SELECT user_id AS id, 'comment' AS engagement_type
      FROM   comments
      WHERE  commentable_id = 48136
      AND    commentable_type = 'Video'

      UNION ALL
      SELECT user_id, 'like'
      FROM   likes
      WHERE  likeable_id = 48136 
      AND    likeable_type = 'Video'
      ) a
   ORDER  BY 1, 2
   LIMIT  10
   ) b
JOIN users u USING (id);

详细信息，链接和说明：

Details, links and explanation:

• 在每行中选择第一行

• Select first row in each GROUP BY group?

如果您要包含所有engagement_types的唯一列表：

If you want a unique list of all engagement_types:

  SELECT id, string_agg(DISTINCT engagement_type, ', ') AS engagement_types
   FROM (
      ...
      ) a
   GROUP  BY 1
   ORDER  BY <whatever>
   LIMIT  10;

string_agg（） 需要Postgres 9.0或更高版本。

这种形式允许按照您想要的顺序进行排序，但是如果您希望 ORDER BY 与 DISTINCT ON 不一致，则需要另一个子查询。

string_agg() need Postgres 9.0 or later.
This form allows to order by whatever you want, while you'd need another subquery if you want ORDER BY to disagree with DISTINCT ON.

这篇关于如何获得独特的用户参与度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！