Google BigQuery - simple function to read XML tags with Regex

 1. Simple SQL function to read XML tags from string using regex

CREATE OR REPLACE FUNCTION `PROJECT.TEST.readXML`(temp1 STRING, tag STRING) RETURNS STRING AS (

REGEXP_SUBSTR(temp1,CONCAT("<",tag,">(.*?)<\\/",tag,">"))

);

 

2. Usage of function

DECLARE example STRING DEFAULT "'<TELEGRAM><Equipment>Welding machine</Equipment><State>SENT</State></TELEGRAM>'";

SELECT 

TEST.readXML(example,"Equipment") as Equipment,

TEST.readXML(example,"State") as State,

TEST.readXML(example,"TELEGRAM") as TELEGRAM

Google BigQuery SQL - calculate production by each hour of the shift

1. Change the date of 3rd shift after midnight to day -1

SELECT  

    -- Change the date of 3rd shift after midnight to day -1

    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date

...

2. Calculate the shift of production

SELECT  

    -- Calculate the shift

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))

    as Shift

...

3. Calculate the time difference between the beginning of the shift and production time

SELECT

 -- Calculate the time difference between the begining of the shift and production date

IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,

IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,

IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,

TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 

as HOUR_OF_PROD

...

4. Create the pivot table

WITH Production as

(

  SELECT  

    WorkCenter, SerialNumber, 

    -- Change the date of 3rd shift after midnight to day -1

    IF (TIME(ProdDateTime) >= '00:00:00' and TIME(ProdDateTime) < '06:00:00',DATE_ADD(DATE(ProdDateTime), INTERVAL -1 DAY),DATE(ProdDateTime)) as Production_Date,

    

    -- Calculate the shift

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00', "1",

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', "2","3"))

    as Shift,

    -- Calculate the time difference between the begining of the shift and production date

    IF (TIME(ProdDateTime) >= '06:00:00' and TIME(ProdDateTime) < '14:00:00',TIME_DIFF(TIME(ProdDateTime),'06:00:00',HOUR)+1,

      IF (TIME(ProdDateTime) >= '14:00:00' and TIME(ProdDateTime) < '22:00:00', TIME_DIFF(TIME(ProdDateTime),'14:00:00',HOUR)+1,

      IF (TIME(ProdDateTime) >= '22:00:00' and TIME(ProdDateTime) <= '23:59:59', TIME_DIFF(TIME(ProdDateTime),'22:00:00',HOUR)+1,

      TIME_DIFF(TIME(ProdDateTime),'00:00:00',HOUR)+1))) 

    as HOUR_OF_PROD

FROM `PROJECT.TEST.TABLE`

)

select * from Production

PIVOT(COUNT(SerialNumber) FOR HOUR_OF_PROD IN (1,2,3,4,5,6,7,8))

5. Results