分组，然后“取消分组”行，并提供附加列以提供分组信息。

huangapple
go评论112阅读模式
英文:

Group, then "ungroup" rows with additional columns giving the group information

问题

Context

假设我们有这个表格数据（在结尾处可以找到一个准备好的请求来创建它）：

  1. +--+-----+-----+--------+
  2. |id|name |color|shape   |
  3. +--+-----+-----+--------+
  4. |1 |john |blue |square  |
  5. |2 |mary |green|square  |
  6. |3 |anna |red  |triangle|
  7. |4 |bob  |blue |square  |
  8. |5 |susan|blue |square  |
  9. |6 |frank|red  |triangle|
  10. +--+-----+-----+--------+

通过这个请求，可以按颜色和形状对行进行分组，更重要的是添加聚合信息：

  1. SELECT
  2.     GROUP_CONCAT(name) AS names,
  3.     color,
  4.     shape,
  5.     COUNT(*) AS nb_duplicates
  6. FROM temp_users
  7. GROUP BY color, shape;

结果：

  1. +--------------+-----+--------+-------------+
  2. |names         |color|shape   |nb_duplicates|
  3. +--------------+-----+--------+-------------+
  4. |john,bob,susan|blue |square  |3            |
  5. |mary          |green|square  |1            |
  6. |anna,frank    |red  |triangle|2            |
  7. +--------------+-----+--------+-------------+

Problem

但是如何才能"解除"行的分组，以便有：

  • 每个用户一行（至少带有其ID，其余可以连接）；
  • 在分组后添加的信息，特别是nb_duplicates和唯一的分组ID（可能是自动递增的）？

Expected output

  1. +--+-----+-----+--------+-------------+------------------+
  2. |id|name |color|shape   |nb_duplicates|duplicate_group_id|
  3. +--+-----+-----+--------+-------------+------------------+
  4. |1 |john |blue |square  |3            |1                 |
  5. |2 |mary |green|square  |1            |2                 |
  6. |3 |anna |red  |triangle|2            |3                 |
  7. |4 |bob  |blue |square  |3            |1                 |
  8. |5 |susan|blue |square  |3            |1                 |
  9. |6 |frank|red  |triangle|2            |3                 |
  10. +--+-----+-----+--------+-------------+------------------+

Similar question

我找到了一个类似的问题https://stackoverflow.com/questions/12128077/mysql-count-group-by-yet-return-all-results，我尝试了提议的方法：

  1. SELECT
  2.     u.*,
  3.     dups.nb_duplicates
  4. FROM temp_users u
  5. INNER JOIN (
  6.     SELECT
  7.         u2.color,
  8.         u2.shape,
  9.         COUNT(*) AS nb_duplicates
  10.     FROM temp_users u2
  11.     GROUP BY color, shape
  12. ) AS dups ON u.color = dups.color AND u.shape = dups.shape;

但我从MySQL收到了这个错误：

> [HY000][1137] 无法重新打开表格：'u'

Example table creation request

只是为了那些想要快速复制表格的人：

  1. DROP TABLE IF EXISTS temp_users;
  2. CREATE TEMPORARY TABLE temp_users (
  3.     id INT AUTO_INCREMENT PRIMARY KEY,
  4.     name VARCHAR(20),
  5.     color VARCHAR(20),
  6.     shape VARCHAR(20)
  7. );
  8. INSERT INTO temp_users(name, color, shape) VALUES
  9. ('john',  'blue',  'square'),
  10. ('mary',  'green', 'square'),
  11. ('anna',  'red',   'triangle'),
  12. ('bob',   'blue',  'square'),
  13. ('susan', 'blue',  'square'),
  14. ('frank', 'red',   'triangle');
英文:

Context

Let's say we have this table data (see at the end for a ready-ot-use request to create it):

  1. +--+-----+-----+--------+
  2. |id|name |color|shape   |
  3. +--+-----+-----+--------+
  4. |1 |john |blue |square  |
  5. |2 |mary |green|square  |
  6. |3 |anna |red  |triangle|
  7. |4 |bob  |blue |square  |
  8. |5 |susan|blue |square  |
  9. |6 |frank|red  |triangle|
  10. +--+-----+-----+--------+

With this request, it's possible to group rows by color and shape, and more importantly to add aggregation information:

  1. SELECT
  2.     GROUP_CONCAT(name) AS names,
  3.     color,
  4.     shape,
  5.     COUNT(*) AS nb_duplicates
  6. FROM temp_users
  7. GROUP BY color, shape;

Result:

  1. +--------------+-----+--------+-------------+
  2. |names         |color|shape   |nb_duplicates|
  3. +--------------+-----+--------+-------------+
  4. |john,bob,susan|blue |square  |3            |
  5. |mary          |green|square  |1            |
  6. |anna,frank    |red  |triangle|2            |
  7. +--------------+-----+--------+-------------+

Problem

But how is it possible to "ungroup" the rows, in order to have:

  • one row per user (at least with its id, the rest can be joined);
  • the information added after the grouping, especially nb_duplicates and a unique group id (maybe auto-incremented) ?

Expected output

  1. +--+-----+-----+--------+-------------+------------------+
  2. |id|name |color|shape   |nb_duplicates|duplicate_group_id|
  3. +--+-----+-----+--------+-------------+------------------+
  4. |1 |john |blue |square  |3            |1                 |
  5. |2 |mary |green|square  |1            |2                 |
  6. |3 |anna |red  |triangle|2            |3                 |
  7. |4 |bob  |blue |square  |3            |1                 |
  8. |5 |susan|blue |square  |3            |1                 |
  9. |6 |frank|red  |triangle|2            |3                 |
  10. +--+-----+-----+--------+-------------+------------------+

Similar question

I found a similar question https://stackoverflow.com/questions/12128077/mysql-count-group-by-yet-return-all-results, I tried the proposition:

  1. SELECT
  2.     u.*,
  3.     dups.nb_duplicates
  4. FROM temp_users u
  5. INNER JOIN (
  6.     SELECT
  7.         u2.color,
  8.         u2.shape,
  9.         COUNT(*) AS nb_duplicates
  10.     FROM temp_users u2
  11.     GROUP BY color, shape
  12. ) AS dups ON u.color = dups.color AND u.shape = dups.shape;

But I got this error from MySql:

> [HY000][1137] Can't reopen table: 'u'

Example table creation request

Just for those who want to quicky reproduce the table:

  1. DROP TABLE IF EXISTS temp_users;
  2. CREATE TEMPORARY TABLE temp_users (
  3.     id INT AUTO_INCREMENT PRIMARY KEY,
  4.     name VARCHAR(20),
  5.     color VARCHAR(20),
  6.     shape VARCHAR(20)
  7. );
  8. INSERT INTO temp_users(name, color, shape) VALUES
  9. ('john',  'blue',  'square'),
  10. ('mary',  'green', 'square'),
  11. ('anna',  'red',   'triangle'),
  12. ('bob',   'blue',  'square'),
  13. ('susan', 'blue',  'square'),
  14. ('frank', 'red',   'triangle');

答案1

得分: 1

  1. SELECT *,
  2.        COUNT(*) OVER (PARTITION BY color, shape) nb_duplicates,
  3.        DENSE_RANK() OVER (ORDER BY color, shape) duplicate_group_id
  4. FROM temp_users
  5. ORDER BY id;
id name color shape nb_duplicates duplicate_group_id
1 john blue square 3 1
2 mary green square 1 2
3 anna red triangle 2 3
4 bob blue square 3 1
5 susan blue square 3 1
6 frank red triangle 2 3

fiddle

英文: 
  1. SELECT *,
  2.        COUNT(*) OVER (PARTITION BY color, shape) nb_duplicates,
  3.        DENSE_RANK() OVER (ORDER BY color, shape) duplicate_group_id
  4. FROM temp_users
  5. ORDER BY id;
id name color shape nb_duplicates duplicate_group_id
1 john blue square 3 1
2 mary green square 1 2
3 anna red triangle 2 3
4 bob blue square 3 1
5 susan blue square 3 1
6 frank red triangle 2 3

fiddle

huangapple
  • 本文由 发表于 2023年7月31日 21:06:14
  • 转载请务必保留本文链接：https://go.coder-hub.com/76803940.html
  • aggregate
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定