Asking Questions & Querying
From the Zing App you’ll see two sections:
- Tables show all visible tables and views of any of your database(s). If you have multiple data sources configured in Zing, you’ll see all visible tables and views from all data sources you have set up or which have been shared with you.
- Questions are questions you save or that other people in your organization have shared with you. Tap a question to view the graph or table of results, or to clone an existing question to make it your own. You can also comment on questions to participate in a conversation, and anybody who is part of that conversation will be notified by email of your comment.
From either a table or a cloned question, select fields and aggregations by tapping and holding on field names. You can tap on a field, hold and drag down – all in one motion – and select an aggregation.
Select a field simply by tapping on it. Blue is selected, grey is unselected.
Tap and hold on a field to see aggregations for that field.
Aggregations and Filters
- Tap and hold a field to see available aggregations and filters, including. Available aggregations and filters are:
- SUM displays the sum of all numeric values
- COUNT displays the number of rows in the database (which meet all the filter conditions across fields in your query)
- AVERAGE displays the mean value across all rows in the database
- GROUP BY groups rows that have the same value – useful for summarizing results by a category (e.g. “Sales by City” if those cities are represented multiple times in a table)
- When a Condition is Met lets you filter rows:
- Equals only returns results which match the value you input (e.g. “City equals ‘New York’” will return results only where the city field for that row equals New York)
- Does Not Equal returns results which do not match the value you input (e.g. “City does NOT equal ‘New York’” would return all cities except New York)
- Greater than returns results which are greater than the value you input
- Greater than or equal returns results which are greater than or equal to the value you input
- Less than returns results which are less than the value you input
- Less than or equal returns results which are less than or equal to the value you input
- Between returns results which are between the two values you specify. For date fields this will be two dates and Zing will display a date selector to easily select dates.
- Includes returns results which include part or all of the value you input. For instance “City includes ‘San’” would return “San Francisco” and “San Diego” if those values are present in the data.
- Excludes returns results which do not include any of the value you input. For instance “City excludes ‘San’” would return all cities which do not contain “San” in their name so “New York” and “Boston” would be part of the result set, but “San Diego” and “San Francisco” would not)
Note that not all fields will show all of the aggregations mentioned above because Zing only shows the aggregations which are relevant – for instance a string field won’t show “greater than” or “less than” as an option because those are only relevant for dates and numeric fields.
Dates and Date Operators
If your database has a date, time, or timestamp field, Zing will show date-specific functionality for filtering and will give you options for grouping by different time scales.
Timestamp fields will by default be shown by day. This means if you GROUP BY a timestamp field, Zing will consider all the timestamps in a given day to be part of that day. You can specify the time scale you’d like – with options for second, minute, hour, day, week, month, and year.
To choose the time scale for a given field, tap the icon in the upper right of the time field as shown below:
To limit the date range of your query you can simply select the date range you want on the pop-up calendar using the following conditions for dates:
- Before lets you specify a date and time with results limited to records before that date/time.
- After lets you specify a date and time with results limited to records after that date/time.
- Between lets you tap between two dates on a calendar to limit the results to that date range
- Not Between lets you tap between two dates on a calendar to exclude results from that time range. This can be useful if, for instance, data was incorrect for a given time range.
If the date/time options outlined above are not showing up for a given field, that field is likely not classified as a date, time, or timestamp.
Create calculated fields by tapping ‘Calculated Field’ below the field names on the question screen.
You’ll then name the field (avoiding the use of spaces), and the calculation. As you type, a autocomplete typeahead will display aggregations and fields.
You can mix and match fields and aggregations in a calculated field. For example, use calculated fields to compute a weighted average, a percentage, or CASE logic.
A question can have multiple calculated fields. To remove a calculated field, tap the ‘X’ on that field, and to edit it, tap ’edit’.
From the main screen, you can search any table names using the search bar at the top of the app.
Once you’ve tapped on a table, you’ll see all fields in that database. You can search across all fields using the search bar at the top of the screen.
You can also use the Zing Custom SQL editor for more sophisticated use cases. A helpful typeahead that auto-completes aggregations like (SUM, COUNT, MIN, MAX, GROUP BY, etc.), and field names.
You can join across tables from the same data source as well.
To access the custom SQL typeahead:
- Select a table from the home screen
- Tap the three dots in the upper left and then ‘Write custom SQL’
- Write your query and then tap ‘Run’ Note: If the administrator of your organization’s account has disabled custom SQL, you won’t see this option.
You can control which fields from your custom SQL query are graphed on the X-axis or Y-axis by using a special aliasing in Zing.
To ensure a field is graphed on the X-axis, add the “_x1” alias to your field in custom SQL. If you also specify “_x2” that _x2 field will be used as the ‘cut’ or ‘split’ for the x-axis field.
To ensure a field is graphed on the Y-axis, add the “_y1”, “_y2”, “y3”, alias to your field name in custom SQL. The underscore and axis designation in your alias won’t show up in your graph titles, but are used to indicate which axis you want a field graphed on.
For example, changing the “_x1” and “_x2” fields in the example below swaps which field is on the x-axis and which field is used as the dimension to split by.
Note that for custom SQL:
- Only ‘select’ statements are supported. Insert / Update / Create / Drop are not.
- For Google BigQuery data sources, tables are referenced as ‘project_id.schema.tablename’ for instance light-quest-122122.austin_bikeshare.bikeshare_trips