如何从两个不同的表中获得两个不同列的总和并对它们进行分组

jm81lzqq 于 2021-08-13 发布在 Java

关注(0)|答案(4)|浏览(330)

我有两个表，我想执行两个表之间的连接，并按日期分组
表1为fbèU数据

id  name created_at           revenue
1   as   2020-05-29 19:30:54  5
2   as   2020-05-28 19:30:54  10
3   ad   2020-05-29 19:31:42  6     
4  ad    2020-05-28 19:31:54 65
5  as    2020-05-29 19:32:10 7
6  ad    2020-05-28 19:31:54 5

表二是谷歌的数据

id name   created_at           sessions
1  as     2020-05-29 19:38:32    5 
2  ad     2020-05-29 19:38:41    10
3  as      2020-05-29 19:38:49   7 
4  as      2020-05-28 19:39:02   6
5  ad     2020-05-28 19:39:13   8
6  ad    2020-05-28 19:41:41   13

现在我想要的是按名称连接两列，然后按创建的\u at对它们进行分组。像这样的

Name , date(created_at) as date , sum(fb_data.revenue) , sum(google_data.revenue)

我有一个sql查询，但它并没有给出预期的结果

select gd.name,fb.total_revenue,fb.created_at,gd.total_sessions
from (select date(fb_data.created_at) as created_at, sum(fb_data.revenue) as total_revenue, name
      from fb_data
      group by date(fb_data.created_at), name) fb,
     (select date(google_data.created_at) as created_at, sum(google_data.session) as total_sessions, name
      from google_data
      group by name, date(google_data.created_at)) as gd
      where fb.name = gd.name
      group by  gd.name,fb.name,fb.created_at,gd.total_sessions

我得到的结果是

Name total_revenue   created_at     total_sessions
as   12              2020-05-29     12             
as   12              2020-05-29     6
as   10              2020-05-28     12
as   10              2020-05-28     6    
ad   6               2020-05-29     10
ad   6              2020-05-29      21
ad  70             2020-05-28       10
ad  70             2020-05-28      21

我期待的结果是

name  total_revenue   created_at total_sessions
as    12              2020-05-29   12 
as    10              2020-05-28   6
ad    6               2020-05-29   10
ad    70              2020-05-28   21

sql mysql aggregate-functions

来源：https://stackoverflow.com/questions/62088750/how-do-i-get-sum-of-two-different-columns-from-two-diffrent-tables-and-group-the

4条答案

按热度按时间

sqserrrh1#

这个问题比乍一看要难一点。
您需要连接三个子查询，每个子查询的名称和日期各一行。
第一个子查询提供所有可能的名称和日期。您需要这个，因为两个详细信息文件中的一个可能缺少另一个包含的某些名称/日期组合

SELECT DISTINCT name, created_at
          FROM (
             SELECT name, DATE(created_at) created_at FROM fb
              UNION
             SELECT name, DATE(created_at) created_at  FROM gg
            ) a

第二个子查询获取每个名称/日期的会话计数。

SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(sessions) sessions
               FROM gg
              GROUP BY name, DATE(created_at)

第三个对收入也是如此。

SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(revenue) revenue
               FROM fb
              GROUP BY name, DATE(created_at)

然后将第二和第三个子查询左键联接到第一个子查询。

SELECT items.name, items.created_at, sess.sessions, rev.revenue
  FROM (first subquery) items
  LEFT JOIN (second subquery) sess ON items.name = sess.name
                                 AND items.created_at = sess.created_at
  LEFT JOIN (third subquery) rev ON items.name = rev.name
                                AND items.created_at = rev.created_at

总而言之，你明白了。

SELECT items.name, items.created_at, sess.sessions, rev.revenue
  FROM (SELECT DISTINCT name, created_at
          FROM (
             SELECT name, DATE(created_at) created_at FROM fb
              UNION
             SELECT name, DATE(created_at) created_at  FROM gg
            ) a
       ) items
  LEFT JOIN (SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(sessions) sessions
               FROM gg
              GROUP BY name, DATE(created_at)
            ) sess ON items.name = sess.name
                  AND items.created_at = sess.created_at
  LEFT JOIN (SELECT name, 
                    DATE(CREATED_AT) created_at, 
                    SUM(revenue) revenue
               FROM fb
              GROUP BY name, DATE(created_at)
            ) rev ON items.name = rev.name
                  AND items.created_at = rev.created_at

https://www.db-fiddle.com/f/hiakasdwn1fuuuff3olbgz/0

赞(0）回复(0）举报 2021-08-13

sycxhyv72#

请使用下面的查询，

select gd.name,fb.total_revenue,fb.created_at, max(gd.total_sessions)
from (select date(fb_data.created_at) as created_at, sum(fb_data.revenue) as 
total_revenue, name
  from fb_data
  group by date(fb_data.created_at), name) fb,
 (select date(google_data.created_at) as created_at, sum(google_data.session) as 
total_sessions, name
  from google_data
  group by name, date(google_data.created_at)) as gd
  where fb.name = gd.name
  group by  gd.name,fb.name,fb.created_at;

赞(0）回复(0）举报 2021-08-13

polkgigr3#

尝试以下查询：

SELECT t.name, t.total_revenue, t.created_at, t2.total_sessions
FROM
  (SELECT name, date(created_at) created_at, sum(revenue) total_revenue
   FROM fb_data
   GROUP BY name,
            date(created_at)) t
INNER JOIN
  (SELECT name, date(created_at) created_at, sum(sessions) total_sessions
   FROM google_data
   GROUP BY name,
            date(created_at)) t2 ON t2.created_at = t.created_at
AND t2.name = t.name;

赞(0）回复(0）举报 2021-08-13

kuuvgm7e4#

您可以使用下面的简单查询。它给出了期望的结果：

select aa.name,aa.created, sum(ses), sum(rev)  
from 
    (select fb_data.name, date(created_at) as created, sum(revenue) as rev, 0 as ses
    from fb_data group by fb_data.name,date(created_at) 
    UNION
    select google_data.name, date(created_at) as created, 0 as rev, sum(sessions) as ses
    from google_data group by google_data.name,date(created_at)
    ) as aa 
group by name,created;

检查结果：https://www.db-fiddle.com/f/7wpgkon7sh58hnf7q4sufh/1

赞(0）回复(0）举报 2021-08-13

我来回答

如何从两个不同的表中获得两个不同列的总和并对它们进行分组

4条答案

相关问题

热门标签

最新问答