Custom chart: Histogram chart executed query SELECT DISTINCT

Report Bugs
1

I create a histogram chart as the example provided by holistic here

But the records count is not correct so I checked the executed query, and found out it uses SELECT DISTINCT. How can I use just SELECT?

#standardsql
SELECT DISTINCT
  `swaps_info`.`amount_out_usd` AS `frsp_aou_2f0bab`
FROM
  `data_warehouse`.`swaps_info_dev` `swaps_info`
3

Hi @Quang_Ng,

While we have the configuration to disable distinct (mentioned in a deleted response), that configuration is highly experimental (not apart of our documented public interface for aml) and after internal testing we find it not really usable since it mess with other other functionality that required distinct values (for example filter suggestion).

In your case, our advice is to change the Custom Chart to require a dimension and a measure instead:

  • a dimension to define the data grain
  • a measure to define the metric that you want to create the histogram on.

This set-up will work for your case while be more reusable. For example, if in your case you want the Histogram of Total amount for each swap then you would drag in the swap_id dimension and the Sum of amount_usd. Since this is more reusable, you can even change the dimension to user_id to get the Histogram of Total amount for each user

Here is an example code for this Custom chart:

CustomChart {
  fields {
    field grain {
      type: 'dimension'
    }
    field metric {
      type: 'measure'
    }
  }
  template: @vgl
  {
    "data": {"values": @{values}},
    "transform": [
      {"bin": {"maxbins": 40}, "field": @{fields.metric.name}, "as": "binned_price"}
    ],
    "mark": {
      "type": "bar",
      "tooltip": true
    },
    "encoding": {
      "x": {"field": "binned_price", "type": "quantitative", "bin": {"binned": true, "step": 20}},
      "x2": {"field": "binned_price_end"},
      "y": {"aggregate": "count"}
    }
  }
  ;;
}```
1 Like
4

Is there any hidden limit in the holistics executed query? (I have a total of more than 3M records)
After I do as you told me, the query automatically applies order by DESC into the query. Then now, the small volumes are not shown in the dashboard anymore.

When I use the SQL editor to check, there are still lots of records that have volumes lower than 1:

image
image1449×868 103 KB

1 Like
5

Hi @Quang_Ng
This is indeed the expected result, since we have a hard limit of 1M records.
Please refer to our doc here for more info: Filtering & Limiting Data | Holistics Docs

I hope this is helpful.

1 Like