Shawn Shawn - 5 months ago 17
SQL Question

d3js: data/column required for time series chart with dropdown list

I would like to build the following time-series chart:

enter image description here

With the following drop-drow lists:

1. Volume category (<50, <100 ...)

2. Time frame (01/01/2016, ...)

3. Industry (entertainment, finance ...)

4. Countries

5. Cities

6. Companies


I refer to Mike Bostock's Multi-Series Line Chart (https://bl.ocks.org/mbostock/3884955).


The data/column used: date, New York, San Francisco, Austin


What are the specific columns I need?

1. Companies

2. Industry

3. Countries

4. Cities

5. Timeframe = for x-axis

6. Volume = count(distinct companies) via group by

7. Volume category = if-condition for Volume


Export MySQL data without getting volume, as for why not, refer to below:


[
{
"company_name": "Axxxx",
"industry": "entertainment",
"country": "USA",
"city": "SF",
"date": "2016-01-01"},
{
"company_name": "Axxxx",
"industry": "entertainment",
"country": "USA",
"city": "SF",
"date": "2016-01-01"},
{
"company_name": "Axxxx",
"industry": "entertainment",
"country": "USA",
"city": "SF",
"date": "2016-01-02"},
{
"company_name": "Bxxxx",
"industry": "entertainment",
"country": "USA",
"city": "SF",
"date": "2016-01-01"},
...]


The problem: If I use "group by" to find the volume, then I will lose the dates for Timeframe. Basically, I am unsure of what data/column I need to extract from MySQL DB to build the chart and drop-down list.

Alternatively, I was thinking of creating the variable volume and volume category after I load the JSON file. Are there any good examples to recommend?

I am currently reading up on building drop-down list and time-series charts examples.

Answer

You seem to be looking for one TSV or json formatted set of data that d3 could re-process to fit any of the changes to the drop down list.

I hope by timeframe, you mean something like: daily, weekly, monthly, quarterly.

If so, then you need to keep your data in the finest of those, group by industry and city only, counting the distinct companies as volume (which means you can't have companies as it's own column), and dropping the predetermined volume category.

Then you could after the fact go through this data with d3 to filter only those volumes for a specific industry, or city, or to sum all volumes for all industries for a filtered city, or sum all volumes for all industries for any city matching a filtered country, etc. And then again for all those if the timeframe is multiples of the smallest unit – 7 dailies for a weekly and 30 dailies for a monthly approximately. Once done you could update the category of volume just summed in the data before rebinding the data to the chart and redrawing it.

In other words, either each change of the drop down will re-request data from the back end service with a specifically tuned sql query for that selection, or you will get one data request per page load and use javascript to adjust the data based on the selections using nest.keys and nest.rollup.