google-bigquery - Bigquery：“table_a”联合所有“table_b”并左连接“table_b”与“mappingstation”表

Question

这是我第一次使用 sql/bigquery。我无法清楚地解释问题，因为我不了解 sql 的逻辑。

我有 'table_a' 我想与 'table_b' 联合，然后查询“TransactionType”、“BusinessDay”和“ExitStationID”。但是，在“table_b”中，“ExitStationID”是“SiteID”，我需要将其与“MP_001_Station”表左连接。

我可以查询两个表。但是，属于“table_b”的 ExitStationID 不会转换为正确的值。

这是代码：

WITH bss AS (
  SELECT
    TransactionType,
    BusinessDay,
    ExitStationID
  FROM
    `table_a`
  WHERE
  EntryStationID IS NOT NULL AND ExitStationID IS NOT NULL AND
  CAST(EntryStationID as INT64) < 100 AND CAST(ExitStationID AS INT64) < 100 AND
  BusinessDay BETWEEN '2017-05-01' AND '2017-12-31'

  UNION ALL
  SELECT
    CAST(MessageTypeValue AS STRING) AS TransactionType,
    BusinessDay,
    SiteID AS ExitStationID
  FROM
    `table_b`
  WHERE
  MessageTypeValue = 20 AND BusinessDay BETWEEN '2017-05-01' AND '2017-12-31'
),

dimdate AS (
  SELECT
    EnglishDayNameOfWeek AS Day,
    DayType,
    FullDateAlternateKey
  FROM
    `DimDate`
),

dimstation AS (
  SELECT
    LineCode AS ExitLine,
    StationKey
  FROM
    `DimStation`
),

mappingstation AS (
  SELECT
    StationCodeBLU
  FROM 
    `MP_001_Station`
),

legacy AS (
  SELECT
    SiteID
  FROM `table_b`
)

SELECT 
  EXTRACT(DAYOFYEAR FROM BusinessDay) AS DayOfYear,
  EXTRACT(DATE FROM BusinessDay) AS Date,
  EXTRACT(MONTH FROM BusinessDay) AS Month,
  Day,
  DayType,
  ExitStationID,
  COUNT(*) AS Ridership

FROM bss
LEFT JOIN dimdate
ON (
  CAST(bss.BusinessDay AS date) = dimdate.FullDateAlternateKey
)

LEFT JOIN dimstation
ON (
  CAST(bss.ExitStationID AS INT64) = CAST(dimstation.StationKey AS INT64)
)

LEFT JOIN mappingstation
ON (
  CAST(bss.ExitStationID AS INT64) = CAST(mappingstation.StationCodeBLU AS INT64)
)

GROUP BY
  DayOfYear,
  Date,
  Month,
  Day,
  DayType,
  ExitStationID

ORDER BY
  DayOfYear,
  ExitStationID

标签： google-bigquery