在进行左联接之前，如何创建日期列表? [英] How do I create a list of dates, before doing a LEFT JOIN?

问题描述

我正在使用BigQuery SQL.我有以下表格:

I am using BigQuery SQL. I have the following tables:

Table "public.org" (records all organisations)
    Column    │          Type          │ Modifiers
──────────────┼────────────────────────┼───────────
 code         │ character varying(6)   │ not null
 name         │ character varying(200) │ not null   
 setting      │ integer                │ not null    

 Table "public.spending" (records spending on chemical by org by month)
    Column         │          Type           │ Modifiers
───────────────────┼─────────────────────────┼───────────
 org_id            │ character varying(6)    │ not null
 month             │ date                    │ not null
 chemical_id       │ character varying(9)    │ not null
 actual_cost       │ double precision        │ not null

我想按组织按月计算特定化学药品的支出.麻烦的是，如果组织在一个月内没有在该化学品上花任何钱，则 spending 表中根本没有条目，而不是零条目.但是，我想要输出(结果为零或零，我不在乎哪个).

And I want to calculate the spending on a particular chemical by month, by organisation. The complication is if there was no spending by an organisation on that chemical in a month, there is simply no entry in the spending table, rather than a zero entry. However, I would like output (a null or zero result, I don't mind which).

现在我有了这个，这给了我所有组织的总支出，包括那些没有条目但没有按月分开支出的组织:

Right now I have this, which gives me total spending for all organisations including those that had no entries, but does not separate spending out by month:

SELECT
  org.code AS code,
  org.name AS name,
  num.actual_cost as actual_cost
FROM (
  SELECT
    code,
    name
  FROM
    org
  WHERE
    setting=4) AS orgs
LEFT OUTER JOIN EACH (
  SELECT
    org_id,
    SUM(actual_cost) AS actual_cost
  FROM
    spending
  WHERE
    chemical_id='1202010U0AAAAAA'
  GROUP BY
    org_id) AS num
ON
  num.org_id = orgs.code

因此，现在我需要扩展它，以便按月和组织进行左加入.我知道我可以通过执行以下操作在 spending 表中获得唯一的月份:

So now I need to extend it to do a LEFT JOIN by month and organisation. I know that I can get the unique months in the spending table by doing this:

SELECT month FROM spending GROUP BY month

(NB BigQuery不支持 UNIQUE .)

(NB BigQuery doesn't support UNIQUE.)

但是如何获得和月组织的所有唯一行，并且只有 then 才能将LEFT JOIN加入支出中?

But how do I get all the unique rows for month and organisation, and only then do a LEFT JOIN onto the spending?

推荐答案

如果我们谈论的是日历月份，那么我们只有12种选择(Jan => Dec).

If we are talking about calendar months there we have only 12 options (Jan => Dec).

只需编译一个静态表或在查询本身中将其编译为12个选择就可以构成一个表，并使用该表进行联接.

Just compile a static table or in the query itself as 12 selects that form a table, and use that to join.

select * from 
(select 1 as m),
(select 2 as m),
....
(select 12 as m)

您可能也对其他帖子中提到的技术感兴趣:

you might also be interested in the Technics mentioned in other posts :

• 如何提取介于BigQuery中有两个时间戳?

Google大查询中的每日点击次数

这篇关于在进行左联接之前，如何创建日期列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！