{
  "uuid": "95e1b58c-0cb7-e508-a75a-358fe4a94e26",
  "last_modified": 1630479636000,
  "version": "3.9.9.1",
  "name": "kylin_sales_cube_test_zyq_delta",
  "is_draft": false,
  "model_name": "kylin_sales_model",
  "description": "",
  "null_string": null,
  "dimensions": [
    {
      "name": "TRANS_ID",
      "table": "KYLIN_SALES",
      "column": "TRANS_ID",
      "derived": null
    },
    {
      "name": "PART_DT",
      "table": "KYLIN_SALES",
      "column": "PART_DT",
      "derived": null
    },
    {
      "name": "SELLER_ID",
      "table": "KYLIN_SALES",
      "column": "SELLER_ID",
      "derived": null
    },
    {
      "name": "BUYER_ID",
      "table": "KYLIN_SALES",
      "column": "BUYER_ID",
      "derived": null
    }
  ],
  "measures": [
    {
      "name": "GMV_SUM",
      "function": {
        "expression": "SUM",
        "parameter": {
          "type": "column",
          "value": "KYLIN_SALES.PRICE"
        },
        "returntype": "decimal(19,4)"
      }
    },
    {
      "name": "TRANS_CNT",
      "function": {
        "expression": "COUNT",
        "parameter": {
          "type": "constant",
          "value": "1"
        },
        "returntype": "bigint"
      }
    },
    {
      "name": "SELLER_CNT_HLL",
      "function": {
        "expression": "COUNT_DISTINCT",
        "parameter": {
          "type": "column",
          "value": "KYLIN_SALES.SELLER_ID"
        },
        "returntype": "hllc(10)"
      }
    },
    {
      "name": "TOP_SELLER",
      "function": {
        "expression": "TOP_N",
        "parameter": {
          "type": "column",
          "value": "KYLIN_SALES.PRICE",
          "next_parameter": {
            "type": "column",
            "value": "KYLIN_SALES.SELLER_ID"
          }
        },
        "returntype": "topn(100,4)",
        "configuration": {
          "topn.encoding.KYLIN_SALES.SELLER_ID": "dict",
          "topn.encoding_version.KYLIN_SALES.SELLER_ID": "1"
        }
      }
    },
    {
      "name": "ITEM_BITMAP",
      "function": {
        "expression": "COUNT_DISTINCT",
        "parameter": {
          "type": "column",
          "value": "KYLIN_SALES.ITEM_ID"
        },
        "returntype": "bitmap"
      }
    },
    {
      "name": "PRICE_PERCENTILE",
      "function": {
        "expression": "PERCENTILE_APPROX",
        "parameter": {
          "type": "column",
          "value": "KYLIN_SALES.PRICE"
        },
        "returntype": "percentile(100)"
      }
    }
  ],
  "dictionaries": [
    {
      "column": "KYLIN_SALES.ITEM_ID",
      "builder": "org.apache.kylin.dict.GlobalDictionaryBuilder",
      "cube": null,
      "model": null
    }
  ],
  "rowkey": {
    "rowkey_columns": [
      {
        "column": "KYLIN_SALES.BUYER_ID",
        "encoding": "integer:4",
        "isShardBy": false
      },
      {
        "column": "KYLIN_SALES.SELLER_ID",
        "encoding": "integer:4",
        "isShardBy": true
      },
      {
        "column": "KYLIN_SALES.TRANS_ID",
        "encoding": "integer:4",
        "isShardBy": false
      },
      {
        "column": "KYLIN_SALES.PART_DT",
        "encoding": "date",
        "isShardBy": false
      }
    ]
  },
  "hbase_mapping": {
    "column_family": [
      {
        "name": "F1",
        "columns": [
          {
            "qualifier": "M",
            "measure_refs": [
              "GMV_SUM",
              "TRANS_CNT"
            ]
          }
        ]
      },
      {
        "name": "F2",
        "columns": [
          {
            "qualifier": "M",
            "measure_refs": [
              "SELLER_CNT_HLL",
              "TOP_SELLER",
              "ITEM_BITMAP",
              "PRICE_PERCENTILE"
            ]
          }
        ]
      }
    ]
  },
  "aggregation_groups": [
    {
      "includes": [
        "KYLIN_SALES.PART_DT",
        "KYLIN_SALES.BUYER_ID",
        "KYLIN_SALES.SELLER_ID",
        "KYLIN_SALES.TRANS_ID"
      ],
      "select_rule": {
        "hierarchy_dims": [],
        "mandatory_dims": [
          "KYLIN_SALES.PART_DT"
        ],
        "joint_dims": []
      }
    }
  ],
  "signature": "iQy/3K2X9Ur4nOI5udjIbg==",
  "notify_list": [],
  "status_need_notify": [],
  "partition_date_start": 1325376000000,
  "partition_date_end": 3153600000000,
  "auto_merge_time_ranges": [],
  "volatile_range": 0,
  "retention_range": 0,
  "engine_type": 6,
  "storage_type": 4,
  "override_kylin_properties": {
    "kylin.cube.aggrgroup.is-mandatory-only-valid": "true",
    "kylin.engine.spark.rdd-partition-cut-mb": "500",
    "kylin.storage.columnar.shard-countdistinct-rowcount": "1000",
    "kylin.cube.source.delta.csv.path": "file:/Users/yaqian.zhang/Desktop/test_csv.csv"
  },
  "cuboid_black_list": [],
  "parent_forward": 3,
  "mandatory_dimension_set_list": [],
  "snapshot_table_desc_list": []
}

# Q1
select TRANS_ID, PART_DT, count(*), sum(price)
from kylin_sales
where TRANS_ID in (9999, 10001, 0, 9998)
group by TRANS_ID, PART_DT

# Q2
SELECT sum(sum_price) AS "COL" 
 FROM ( 
 select kylin_sales.part_dt, sum(kylin_sales.price) as sum_price, count(distinct ITEM_ID) as cnt_1
 from kylin_sales 
 group by kylin_sales.part_dt 
 ) "TableauSQL" 
 HAVING COUNT(1)>0

# Q3
SELECT TRANS_ID, PART_DT, SUM(PRICE) AS GMV,
FIRST_VALUE(SUM(PRICE)) OVER(PARTITION BY PART_DT ORDER BY PART_DT) AS "FIRST",
LAST_VALUE(SUM(PRICE)) OVER(PARTITION BY PART_DT ORDER BY PART_DT) AS "CURRENT",
LAG(SUM(PRICE), 1, 0.0) OVER(PARTITION BY PART_DT ORDER BY PART_DT) AS "PREV",
LEAD(SUM(PRICE), 1, 0.0) OVER(PARTITION BY PART_DT ORDER BY PART_DT) AS "NEXT",
NTILE(4) OVER (PARTITION BY PART_DT ORDER BY PART_DT) AS "QUARTER"
FROM KYLIN_SALES
INNER JOIN KYLIN_ACCOUNT as SELLER_ACCOUNT
ON KYLIN_SALES.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID
INNER JOIN KYLIN_COUNTRY as SELLER_COUNTRY
ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY
where TRANS_ID in (9999, 10001, 0, 9998)
GROUP BY TRANS_ID, PART_DT
ORDER BY PART_DT;

# Q4
select TRANS_ID, PART_DT, count(*), sum(price), count(distinct ITEM_ID), percentile(price, 0.5)
from kylin_sales
where TRANS_ID in (9999, 10001, 0, 9998)
group by TRANS_ID, PART_DT