wawrzyk3.blogspot.com: June 2022

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