sql - 将天数分组为单行(汇总)

Question

我在显示一份报告时遇到了一些困难，该报告既可以将所有日期分组到月中，又可以对月中每天的“支出”求和。

我的 SQL 查询创建了这个:(检索所有日期)

  Date                            Name                Spend
    2018-01-03 00:00:00.000000  xxxxxxxxx   34.699999999999996
    2018-01-03 00:00:00.000000  xxxxxxxxx   31.89999999999999
    2018-01-03 00:00:00.000000  xxxxxxxxx   29.49
    2018-01-03 00:00:00.000000  xxxxxxxxx   52.15
    2018-01-03 00:00:00.000000  xxxxxxxxx   101.07000000000001
    2018-01-03 00:00:00.000000  xxxxxxxxx   104.37999999999998

我想要的是:(检索月而不是天)

    Month        Name                Spend
    2018-01  xxxxxxxxx   34.69999999999999(summed monthly)
    2018-02  xxxxxxxxx   31.89999999999999(summed monthly)
    2018-03  xxxxxxxxx   29.49(summed monthly)
    2018-04  xxxxxxxxx   52.15(summed monthly)
    2018-05  xxxxxxxxx   101.07000000000001(summed monthly)
    2018-06  xxxxxxxxx   104.37999999999998(summed monthly)

这是我的 SQL 查询:

SELECT
  "GregorianDate" as date_year,
  "AccountName" as account_name,
  sum("Spend") as spend
FROM bingads_redperformance.keyword_performance_report
WHERE
  EXTRACT(YEAR FROM ("GregorianDate")) = 2018
GROUP BY date_year, account_name
LIMIT 1000

我尝试添加 MONTH(date_year) 但没有成功。我希望有人能指导我正确的方向。

Answer 1

我用示例数据对此进行了测试。工作正常。

SELECT
  TO_CHAR("GregorianDate", 'YYYY-MM') as month,
  "AccountName" as account_name,
  SUM("Spend") as spend
FROM bingads_redperformance.keyword_performance_report
WHERE
  EXTRACT(YEAR FROM ("GregorianDate")) = 2018
GROUP BY TO_CHAR("GregorianDate", 'YYYY-MM'),"AccountName" ;