如何仅计算值的首次出现? [英] How do I count only the first occurrence of a value?

问题描述

我有一张这样的桌子；

I have a table like this;

+----+---------+-------------+
| id | user_id | screenWidth |
+----+---------+-------------+
|  1 |       1 |        1366 |
|  2 |       1 |        1366 |
|  3 |       1 |        1366 |
|  4 |       1 |        1366 |
|  5 |       2 |        1920 |
|  6 |       2 |        1920 |
|  7 |       3 |        1920 |
|  8 |       4 |        1280 |
|  9 |       5 |        1280 |
| 10 |       6 |        1280 |
+----+---------+-------------+

与其他数据一起加载.如果需要，可以将其标准化，起初我认为我不需要，但也许应该.无论如何，

Along with loads of other data. This could be normalised if needed, originally I didn't think I'd need to, but perhaps I should. Anyway,

我想要一个仅对每个用户计数一次screenWidth值的查询，因此输出如下所示:

I'd like a query that only counts the screenWidth values once per user, so the output would look like:

+-------------+-------+
| screenWidth | count |
+-------------+-------+
|        1366 |     1 |
|        1920 |     2 |
|        1280 |     3 |
+-------------+-------+

而不是将1366计为4，这可以避免繁重的用户歪曲数据.

Rather than counting 1366 as 4 - this would avoid heavy users from skewing the data.

是否可以编写查询来做到这一点?

Is there a way to write a query to do this?

推荐答案

您必须获取每个屏幕宽度的DISTINCT用户数，这是将获取结果的示例查询.

You have to get the DISTINCT count of users per screenwidth and here is the sample query that will fetch the results.

点击此处查看SQL Fiddle中的演示

脚本:

CREATE TABLE screenwidth 
(
    id INT NOT NULL
  , user_id INT NOT NULL
  , screenwidth INT NOT NULL
);

INSERT INTO screenwidth (id, user_id, screenwidth) VALUES
  (1, 1, 1366),
  (2, 1, 1366),
  (3, 1, 1366),
  (4, 1, 1366),
  (5, 2, 1920),
  (6, 2, 1920),
  (7, 3, 1920),
  (8, 4, 1280),
  (9, 5, 1280),
  (10, 6, 1280);

SELECT      screenwidth
        ,   COUNT(DISTINCT user_id) AS screenwidthcount
FROM        screenwidth
GROUP BY    screenwidth
ORDER BY    screenwidthcount;

输出:

SCREENWIDTH SCREENWIDTHCOUNT
----------- ----------------
1366               1
1920               2
1280               3

这篇关于如何仅计算值的首次出现?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！