主页 / dba / 问题 / 190866
Accepted
Mio
Asked: 2017-11-15 02:49:07 +0800 CST

对不同和分组求和

  • 772

我有这个 ：

CREATE TABLE tickets(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL, shop_id int NOT NULL, created_at time DEFAULT current_time);
INSERT INTO tickets VALUES (0, 'web-mobile', 1, 2);
INSERT INTO tickets VALUES (1, 'web-destkop', 1, 2);
INSERT INTO tickets VALUES (2, 'web-destkop', 2, 2);

SELECT shop_id, source, COUNT(DISTINCT(user_id)) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, source;

雷克斯斯特：http: //rextester.com/TDS9913

但我想要的是得到 distinct user_idon的总和web-*。

shop_id source  user_count
2       web     2
postgresql sum

2 个回答

  1. Best Answer

    您需要按源的子字符串分组：

    SELECT shop_id, 
       substring(source,1,3), 
       COUNT(DISTINCT user_id) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, substring(source,1,3);

    http://rextester.com/EFZZSG27212

    • 4

  2. 你想要的是这样的：

    SELECT   
  shop_id, 
  user_id,  
  COUNT(user_id)  AS user_count, 
  LEFT(source, 4) AS new_source
FROM tickets
GROUP BY (user_id, shop_id, new_source)
ORDER BY user_id;

    结果：（在此处检查 db-fiddle ）

    shop_id     user_id     user_count  new_source
      2           1              2        web-
      2           2              1        web-

    当然，您可以按照建议使用SUBSTRING而不是。LEFT

    • 0

相关问题