Блог

У меня есть задачи в BigQuery с датой создания и датой последнего изменения. Я хотел бы иметь возможность сообщать о количестве событий открытия и закрытия задач по дате в одной таблице, если это возможно.

 view: tasks {
  derived_table: {
    sql:
        SELECT *
        FROM UNNEST(ARRAY<STRUCT<CREATED_DATE DATE, LAST_MODIFIED DATE, ID INT64, STATE STRING>>[
        ('2020-12-01', '2020-12-01', 1, "OPEN"),
        ('2020-12-01', '2020-12-03', 2, "CLOSED"),
        ('2020-12-02', '2020-12-03', 3, "CLOSED"),
        ('2020-12-03', '2020-12-05', 4, "OPEN"),
        ('2020-12-05', '2020-12-05', 5, "CLOSED")])
      ;;
  }
  dimension_group: created {
    type: time
    datatype: date
    sql: ${TABLE}.created_date ;;
  }
  dimension_group: last_modified {
    type: time
    datatype: date
    sql: ${TABLE}.last_modified ;;
  }
  dimension: id {
    type: number
  }
  dimension: state {
    type: string
  }
  measure: number_of_tasks {
    type: count_distinct
    sql: ${id} ;;
  }
  measure: number_of_open_tasks {
    type: count_distinct
    sql: ${id} ;;
    filters: {
      field: "state"
      value: "OPEN"
    }
  }
  measure: number_of_closed_tasks {
    type: count_distinct
    sql: ${id} ;;
    filters: {
      field: "state"
      value: "CLOSED"
    }
  }
}
explore: tasks {}
 

Я могу получить количество открытых задач, используя созданную дату.

Я могу получить количество закрытых задач путем подсчета задач, где дата последнего изменения указана в периоде агрегирования, а статус закрыт, с отфильтрованной мерой.

Однако, если я попытаюсь объединить их в одну таблицу, я получу строку для каждой комбинации дат.

Как я могу посчитать изменения состояния задачи по дате?

Коллега предложил решение. Наложение таблицы задач на себя создает (до) двух строк на задачу.

 view: tasks {
  derived_table: {
    sql:
        WITH tab AS (
          SELECT *
          FROM UNNEST(ARRAY<STRUCT<CREATED_DATE DATE, LAST_MODIFIED DATE, ID INT64, STATE STRING>>[
          ('2020-12-01', '2020-12-01', 1, "OPEN"),
          ('2020-12-01', '2020-12-03', 2, "CLOSED"),
          ('2020-12-02', '2020-12-03', 3, "CLOSED"),
          ('2020-12-03', '2020-12-05', 4, "OPEN"),
          ('2020-12-05', '2020-12-05', 5, "CLOSED")])
        )
        SELECT *, 1 open_count, 0 closed_count, created_date AS action_date 
        FROM tab
        UNION DISTINCT
        SELECT *, 0 open_count, 1 closed_count, last_modified AS action_date 
        FROM tab
        WHERE state = "CLOSED"
      ;;
  }
  dimension_group: created {
    type: time
    datatype: date
    sql: ${TABLE}.created_date ;;
  }
  dimension_group: last_modified {
    type: time
    datatype: date
    sql: ${TABLE}.last_modified ;;
  }
  dimension_group: action {
    type: time
    datatype: date
    sql: ${TABLE}.action_date ;;
  }
  dimension: id {
    type: number
  }
  dimension: state {
    type: string
  }
  dimension: open_count {
    type: number
    hidden: yes
  }
  dimension: closed_count {
    type: number
    hidden: yes
  }
  measure: number_opened{
    type: sum
    sql: ${open_count} ;;
  }
  measure: number_closed {
    type: sum
    sql: ${closed_count} ;;
  }
}
explore: tasks {}
 

Затем можно подсчитать открытые и закрытые теги.