postgresql分组依据

Question

这是我在Postgresql中的查询：

SELECT 
    C.id, S.domen, B.name, C.source_id, ST.name 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') 
ORDER BY "calltime" LIMIT 50

我得到的结果是：

​

​

现在，我尝试按名称(最后一列)对此结果进行分组，以获得如下结果：

Контекстная реклама - 17
SEO-10
.... 

为此，我使用以下查询：

SELECT 
    ST.name, count(ST.name) 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') 
GROUP BY ST.name 
ORDER BY count(ST.name) DESC LIMIT 50

但是我得到了错误的结果：

​

​

它似乎是从source_id列中获取值。我做错了什么？

Answer 1

试试看，如果你在限制结果之前调用group by，然后对整个记录调用group by。因此，首先筛选，然后执行分组依据。

    SELECT 
        name, count(name) 
    FROM(
    SELECT 
        ST.name 
    FROM "calls" C 
        INNER JOIN "site" S ON S.id=C.site_id 
        INNER JOIN "sources" ON sources.id=C.source_id 
        INNER JOIN "brand" B ON B.id = S.id_brand
        INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
    WHERE 
        ("calltime" >= '2017-12-01') AND 
        ("calltime" <= '2017-12-03') AND 
        (S."id_brand"='6') 
    ORDER BY "calltime" LIMIT 50
    ) T 
    GROUP BY name 

Answer 2

49号可能只是个巧合。尝试：

SELECT 
    ST.name, count(*) 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') AND 
    C.source_id > 50
GROUP BY ST.name 
ORDER BY count(*) DESC LIMIT 50

我所做的就是将其更改为count(*)并将AND C.source_id > 50添加到Where子句中。让我们看看这是否会改变计数。

您也可以只运行以下命令：

SELECT 
    count(*) 
FROM "calls" C 
    INNER JOIN "site" S ON S.id=C.site_id 
    INNER JOIN "sources" ON sources.id=C.source_id 
    INNER JOIN "brand" B ON B.id = S.id_brand
    INNER JOIN "source_types" ST ON ST.id = "sources".type_id 
WHERE 
    ("calltime" >= '2017-12-01') AND 
    ("calltime" <= '2017-12-03') AND 
    (S."id_brand"='6') 

这将给出所有行的总计数(我删除了GROUP BY)。如果这个数字等于分组行计数的总和，那么它们就是真正的计数。我们正在寻找的是49 + 30 + 21 + 13 + 9 + 4 + 1的127

我希望这能帮到你。

Answer 3

第二个查询中的LIMIT 50在GROUP BY之后执行。

如果只想聚合前50行，请将LIMIT 50写入子选择并在外部SELECT中执行GROUP BY。