CODEWITHSUNDEEP
google-bigquery
Shivi Bhatia
Shivi Bhatia

Reputation: 189

Breaking A column into multiple column

Request help with Google Big Query
We have a data table where one of the column is status which captures the
lifecycle of a ticket.
This field has ticket assigned date, work in progress, closed, pending etc.
various categories. The example below: 'Assigned (03/01/2017 06:13:47 AM) -> Work In Progress (03/02/2017 05:27:52 AM) -> Resolved (04/06/2017 03:34:16 AM)' we need to create multiple columns basis this column - one for assigned, another
for resolved etc. We have tried some options as below

substr(STATUS_TRAIL,11,20) assigned_date, right(STATUS_TRAIL,34) as date,

But with this the result is not current as every column may miss one of the
other status i.e. some ticket could not closed yet or any not pending and are WIP whereas some are closed.

Upvotes: 1

Views: 114

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Try the SPLIT function:

#standardSQL
WITH Input AS (
   SELECT 'Assigned (03/01/2017 06:13:47 AM) -> Work In Progress (03/02/2017 05:27:52 AM) -> Resolved (04/06/2017 03:34:16 AM)' AS STATUS_TRAIL
)
SELECT
  events[SAFE_OFFSET(0)] AS assigned_event,
  events[SAFE_OFFSET(1)] AS progress_event,
  events[SAFE_OFFSET(2)] AS resolved_event
FROM (
  SELECT
    SPLIT(STATUS_TRAIL, ' -> ') AS events
  FROM Input
);

Or, as another way of looking at your data, you could model it as an array with entries for the event type and the timestamp:

#standardSQL
WITH Input AS (
   SELECT 'Assigned (03/01/2017 06:13:47 AM) -> Work In Progress (03/02/2017 05:27:52 AM) -> Resolved (04/06/2017 03:34:16 AM)' AS STATUS_TRAIL
)
SELECT
  ARRAY(
    SELECT AS STRUCT
      parts[SAFE_OFFSET(0)] AS type,
      PARSE_TIMESTAMP('%m/%d/%Y %T %p)', parts[SAFE_OFFSET(1)]) AS timestamp
    FROM (
      SELECT SPLIT(event_string, ' (') AS parts
      FROM UNNEST(event_strings) AS event_string
    )
  ) AS events
FROM (
  SELECT SPLIT(STATUS_TRAIL, ' -> ') AS event_strings
  FROM Input
);

Each row in the output will look something like:

[{Assigned, 2017-03-01 06:13:47+00},
 {Work In Progress, 2017-03-02 05:27:52+00},
 {Resolved, 2017-04-06 03:34:16+00}]

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Try below for BigQuery Standard SQL 

#standardSQL
WITH yourTable AS (
  SELECT 'Assigned (03/01/2017 06:13:47 AM) -> Work In Progress (03/02/2017 05:27:52 AM) -> Resolved (04/06/2017 03:34:16 AM)' AS ticket 
)
SELECT 
  REGEXP_EXTRACT(ticket, r'Assigned \((\d\d/\d\d/\d\d\d\d \d\d:\d\d:\d\d [AP]M)\)') AS assigned,
  REGEXP_EXTRACT(ticket, r'Work In Progress \((\d\d/\d\d/\d\d\d\d \d\d:\d\d:\d\d [AP]M)\)') AS inprogress,
  REGEXP_EXTRACT(ticket, r'Resolved \((\d\d/\d\d/\d\d\d\d \d\d:\d\d:\d\d [AP]M)\)') AS resolved
FROM yourTable

Upvotes: 2

Related Questions