| SELECT
|
| F.IMEI,
|
| F.STATUS,
|
| BBD.BUS_TYPE,
|
| BBD.BUS_NUMBER,
|
| BBD.DEPOT,
|
| BBD.CITY,
|
| V1."timestamp" AS V1_TIMESTAMP,
|
| V1."B2V_MaxCellV1" AS MAX_CELL_V1,
|
| V1."B2V_MinCellV1" AS MIN_CELL_V1,
|
| V2."timestamp" AS V2_TIMESTAMP,
|
| V2."B2V_MaxCellV2" AS MAX_CELL_V2,
|
| V2."B2V_MinCellV2" AS MIN_CELL_V2,
|
| V3."timestamp" AS V3_TIMESTAMP,
|
| V3."B2V_MaxCellV3" AS MAX_CELL_V3,
|
| V3."B2V_MinCellV3" AS MIN_CELL_V3,
|
| V4."timestamp" AS V4_TIMESTAMP,
|
| V4."B2V_MaxCellV4" AS MAX_CELL_V4,
|
| V4."B2V_MinCellV4" AS MIN_CELL_V4,
|
| T1."timestamp" AS T1_TIMESTAMP,
|
| T1."B2V_MaxCellT1" AS MAX_CELL_T1,
|
| T1."B2V_MinCellT1" AS MIN_CELL_T1,
|
| T2."timestamp" AS T2_TIMESTAMP,
|
| T2."B2V_MaxCellT2" AS MAX_CELL_T2,
|
| T2."B2V_MinCellT2" AS MIN_CELL_T2,
|
| T3."timestamp" AS T3_TIMESTAMP,
|
| T3."B2V_MaxCellT3" AS MAX_CELL_T3,
|
| T3."B2V_MinCellT3" AS MIN_CELL_T3,
|
| T4."timestamp" AS T4_TIMESTAMP,
|
| T4."B2V_MaxCellT4" AS MAX_CELL_T4,
|
| T4."B2V_MinCellT4" AS MIN_CELL_T4,
|
| SOC."B2V_SOC" AS SOC,
|
| KWH."B2V_RateEgy" AS KWH
|
| FROM
|
| (
|
| SELECT
|
| IMEI,
|
| STATUS,
|
| START
|
| FROM
|
| BUS_LATEST.FAULTS
|
| WHERE
|
| STATUS = 'open'
|
| ) F
|
| LEFT JOIN BUS_LATEST.BUS_BATTERY_DATA BBD
|
| ON F.IMEI = BBD.IMEI
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellV1",
|
| "B2V_MinCellV1"
|
| FROM
|
| GOODENOUGH."1012a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS V1
|
| ON F.IMEI = V1."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellV2",
|
| "B2V_MinCellV2"
|
| FROM
|
| GOODENOUGH."1013a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS V2
|
| ON F.IMEI = V2."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellV3",
|
| "B2V_MinCellV3"
|
| FROM
|
| GOODENOUGH."1014a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS V3
|
| ON F.IMEI = V3."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellV4",
|
| "B2V_MinCellV4"
|
| FROM
|
| GOODENOUGH."1015a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS V4
|
| ON F.IMEI = V4."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellT1",
|
| "B2V_MinCellT1"
|
| FROM
|
| GOODENOUGH."1016a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS T1
|
| ON F.IMEI = T1."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellT2",
|
| "B2V_MinCellT2"
|
| FROM
|
| GOODENOUGH."1017a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS T2
|
| ON F.IMEI = T2."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellT3",
|
| "B2V_MinCellT3"
|
| FROM
|
| GOODENOUGH."1018a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS T3
|
| ON F.IMEI = T3."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_MaxCellT4",
|
| "B2V_MinCellT4"
|
| FROM
|
| GOODENOUGH."1019a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| ) AS T4
|
| ON F.IMEI = T4."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_SOC"
|
| FROM
|
| GOODENOUGH."1820a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| )AS SOC
|
| ON F.IMEI = SOC."IMEI"::TEXT
|
| LEFT JOIN LATERAL (
|
| SELECT
|
| "IMEI",
|
| "timestamp",
|
| "B2V_RateEgy"
|
| FROM
|
| GOODENOUGH."1823a1f3"
|
| WHERE
|
| "IMEI"::TEXT = F.IMEI
|
| AND TIMESTAMP <= F.START
|
| ORDER BY
|
| TIMESTAMP DESC LIMIT 1
|
| )AS KWH
|
| ON F.IMEI = KWH."IMEI"::TEXT
|