allanbatista
9/26/2018 - 8:54 PM

BIGQUERY get most recent results by timestamp

this get most recente results by timestamp and count how manu seconds was expaned

gs__my_bucket_my_function.js
content_copyfile_download
/**
 * Format Currect Data
 *
 * @param {string} inJson
 * @return {string} outJson
 */
function transform(inJson) {
  var obj = JSON.parse(inJson);

  // Limitação do scopo somente para b2w, skyhun e preço
  if( ( obj.target !== 'b2w' || obj.target !== 'skyhub' ) && new_obj.action !== 'sent_product_price_to_mktp' ) {
    return;
  }

  var new_obj = {
    account_id: obj.account_id,
    sku: obj.external_id,
    action: obj.target === 'skyhub' ? 'sent_product_price_to_mktp' : obj.action,
    target: obj.target,
    created_at: obj.timestamp
  }

  return JSON.stringify(new_obj);
}
schema.json
content_copyfile_download
{
  "account_id": "account_id",
  "sku": "sku",
  "action": "price",
  "target": "b2w",
  "created_at": "2018-09-27T11:28:27-03:00"
}
README.md
content_copyfile_download
  • Rendered
  • Source

Queries

Esperando atualização

SELECT account_id, sku, action, target, created_at
FROM (
 SELECT account_id, sku, action, target, created_at
      , RANK() OVER(PARTITION BY account_id, sku, action ORDER BY created_at DESC) rank
 FROM `lunar-sled-217521.skyhub.timeline_metrics`
)
WHERE rank=1 and target = "skyhub"

Last B2w Update

SELECT * FROM (
  SELECT
    account_id, sku, action, target, created_at,
    RANK() OVER(PARTITION BY account_id, sku, action ORDER BY created_at DESC) rank
  FROM (
    SELECT
      t1.account_id, t1.sku, t1.action, t1.target, t1.created_at
    FROM
      `lunar-sled-217521.skyhub.timeline_metrics` as t1
    JOIN
      `lunar-sled-217521.skyhub.timeline_metrics_waiting_update` as t2
    ON
      t1.account_id = t2.account_id and t1.sku = t2.sku and t1.action = t2.action
    WHERE
      t1.target = 'b2w'
  )
) WHERE rank=1

LIST all first updates wating to go to b2w

SELECT
  account_id, sku, action, target, created_at
FROM (
  SELECT
    t3.account_id, t3.sku, t3.action, t3.target, t3.created_at,
    RANK() OVER(PARTITION BY t3.account_id, t3.sku, t3.action ORDER BY t3.created_at ASC) rank
  FROM
    `lunar-sled-217521.skyhub.timeline_metrics` t3
  JOIN
    `lunar-sled-217521.skyhub.timeline_metrics_last_b2w_updates` as t4
  ON 
    t3.account_id = t4.account_id and t3.sku = t4.sku and t3.action = t4.action
  WHERE
    t3.target = 'skyhub' and
    t3.created_at > t4.created_at
)
WHERE
  rank=1

LIST all pending updates from first update

SELECT
  account_id, sku, action, target, created_at,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), created_at, SECOND) as seconds_opening
FROM (
  SELECT 
    account_id, sku, action, target, created_at,
    RANK() OVER(PARTITION BY account_id, sku, action ORDER BY created_at ASC) rank
  FROM (
    SELECT
      account_id, sku, action, target, created_at
    FROM
      `lunar-sled-217521.skyhub.timeline_metrics_waiting_update_first_insert`
    UNION ALL
    SELECT
      account_id, sku, action, target, created_at   
    FROM `lunar-sled-217521.skyhub.timeline_metrics_waiting_update`
  )
) WHERE rank = 1

INSERT HELPER

INSERT INTO `lunar-sled-217521.skyhub.timeline_metrics`
  (account_id, sku, action, target, created_at, inserted_at)
VALUES ("account_id_2", "sku_2", "price", "skyhub", CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())
script.SQL
content_copyfile_download
SELECT
  account_id,
  sku,
  action,
  target,
  created_at,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), created_at, SECOND) as seconds_opening
FROM (
 SELECT
  account_id,
  sku,
  action,
  target,
  created_at,
  RANK() OVER(PARTITION BY account_id, sku, action ORDER BY created_at DESC) rank
 FROM `table_name`
)
WHERE rank=1 and target = "skyhub"