Writing SQL Queries

1. Writing Basic SELECT Queries

Component	Description	Example
SELECT	Specifies the columns to retrieve from a table	`SELECT name, age FROM users;`
FROM	Indicates the table to query data from	`FROM employees;`
WHERE	Filters rows based on a condition	`WHERE age > 30;`
ORDER BY	Sorts the result set by one or more columns	`ORDER BY salary DESC;`
LIMIT	Restricts the number of rows returned	`LIMIT 10;`

Example: Basic SELECT Query

SELECT name, email
FROM users
WHERE active = 1
ORDER BY created_at DESC
LIMIT 5;

Note: In Grafana, SQL queries are executed against data sources like MySQL, PostgreSQL, or InfluxDB. Ensure the query returns a valid result set that matches the expected schema.

2. Using WHERE Clauses

Clause	Description	Example
WHERE	Filters rows based on a condition	`SELECT * FROM logs WHERE level = 'ERROR'`
AND	Combines multiple conditions, all must be true	`SELECT * FROM logs WHERE level = 'ERROR' AND timestamp > '2024-01-01'`
OR	Combines multiple conditions, at least one must be true	`SELECT * FROM logs WHERE level = 'ERROR' OR level = 'WARNING'`
NOT	Negates a condition	`SELECT * FROM logs WHERE NOT level = 'INFO'`
IN	Matches any value in a list	`SELECT * FROM logs WHERE status IN ('200', '201')`
BETWEEN	Matches values within a range	`SELECT * FROM logs WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'`
LIKE	Matches patterns using wildcards	`SELECT * FROM logs WHERE message LIKE '%error%'`
IS NULL	Checks for null values	`SELECT * FROM logs WHERE user_id IS NULL`
IS NOT NULL	Checks for non-null values	`SELECT * FROM logs WHERE user_id IS NOT NULL`

Example: Filtering Logs by Error Level and Time Range

SELECT timestamp, level, message
FROM logs
WHERE level = 'ERROR'
AND timestamp BETWEEN '2024-01-01T00:00:00Z' AND '2024-01-31T23:59:59Z'
ORDER BY timestamp DESC;

3. Using JOIN Operations

JOIN Type	Description	Example
INNER JOIN	Returns rows that have matching values in both tables	`SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;`
LEFT JOIN	Returns all rows from the left table and matched rows from the right table	`SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;`
RIGHT JOIN	Returns all rows from the right table and matched rows from the left table	`SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;`
FULL OUTER JOIN	Returns all rows from both tables with matching records	`SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.id = t2.id;`

Example: Combining Data with JOINs

SELECT u.name, o.order_date, o.total
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.order_date >= '2023-01-01';

Note: JOINs can significantly impact query performance. Use indexes on joined columns for better performance.

4. Using Time Macros ($timeFilter, $timeFrom)

Macro	Description	Usage
$__timeFilter	Generates a time filter condition for SQL queries.	`WHERE timestamp >= $__timeFrom AND timestamp <= $__timeTo`
$__timeFrom	Represents the start time of the current query range in milliseconds.	`SELECT * FROM logs WHERE timestamp >= $__timeFrom`
$__timeTo	Represents the end time of the current query range in milliseconds.	`SELECT * FROM logs WHERE timestamp <= $__timeTo`

Example: Using Time Macros in a Query

SELECT 
  timestamp,
  message
FROM 
  logs
WHERE 
  timestamp >= $__timeFrom 
  AND timestamp <= $__timeTo
ORDER BY 
  timestamp DESC;

Note: Time macros are automatically replaced with actual timestamps when the query is executed.

5. Using Interval Macros ($interval, $interval_ms)

Macro	Description	Example Value	Notes
$__interval	Auto-calculated interval for time series queries	`1m`	Used for dynamic grouping and aggregation in queries
$__interval_ms	Auto-calculated interval in milliseconds	`60000`	Useful for precise time-based calculations in queries

Example: Using $__interval in a SQL Query

SELECT
  date_trunc('hour', time) AS hour,
  avg(cpu_usage) AS avg_cpu
FROM
  system_metrics
WHERE
  time >= $__timeFrom() AND time <= $__timeTo()
GROUP BY
  hour
ORDER BY
  hour

Example: Using $__interval_ms in a SQL Query

SELECT
  time_bucket('1m', time) AS bucket,
  avg(cpu_usage) AS avg_cpu
FROM
  system_metrics
WHERE
  time >= $__timeFrom() AND time <= $__timeTo()
GROUP BY
  bucket
ORDER BY
  bucket

6. Formatting as Time Series

Option	Values	Description
time_series	`true`	Formats query results as time series data for Grafana visualization
time_column	`timestamp`	Name of the column containing time values (default: `time`)
value_column	`value`	Name of the column containing numeric values (default: `value`)
metric_column	`metric`	Name of the column containing metric labels (optional)

Example: Time Series Query

SELECT 
  timestamp AS time,
  value AS metric_value,
  label AS metric_label
FROM metrics_table
WHERE timestamp >= '2024-01-01'
ORDER BY timestamp

Note: Grafana expects time series data in a specific format with timestamps and numeric values for proper visualization.

7. Formatting as Table

Format	Description	Usage
table	Displays query results in a standard tabular format	Default format for most queries
time_series	Formats results for time-series visualization	Used with time-based data and charting
logs	Displays query results as log entries	Best for log data analysis and filtering
trace	Formats results for trace visualization	Used with distributed tracing systems like Jaeger or Zipkin

Example: Using Table Format in SQL Query

SELECT 
  "timestamp",
  "metric_name",
  "value"
FROM 
  "metrics_table"
WHERE 
  "timestamp" >= now() - 1h
ORDER BY 
  "timestamp"

Note: The table format is ideal for displaying structured data with multiple columns and rows, making it easy to analyze and filter results.

8. Using Aggregate Functions

Function	Description	Example
COUNT()	Counts the number of rows or non-NULL values	`SELECT COUNT(*) FROM logs;`
SUM()	Returns the sum of numeric values	`SELECT SUM(bytes) FROM network_logs;`
AVG()	Calculates the average of numeric values	`SELECT AVG(cpu_usage) FROM system_metrics;`
MIN()	Returns the minimum value in a set	`SELECT MIN(temp_celsius) FROM weather_data;`
MAX()	Returns the maximum value in a set	`SELECT MAX(memory_usage_mb) FROM app_stats;`
GROUP_CONCAT()	Concatenates values from multiple rows into one string	`SELECT GROUP_CONCAT(tag SEPARATOR ', ') FROM events;`

Note: Aggregate functions are often used with GROUP BY to summarize data by categories or time ranges.

9. Using Window Functions

Function	Description	Example
ROW_NUMBER()	Assigns a unique sequential number to each row	`SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, name, salary FROM employees;`
RANK()	Assigns ranks, with gaps for ties	`SELECT RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank, name, salary FROM employees;`
DENSE_RANK()	Assigns ranks without gaps for ties	`SELECT DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank, name, salary FROM employees;`
LAG()	Accesses previous row's value in partition	`SELECT name, salary, LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary FROM employees;`
LEAD()	Accesses next row's value in partition	`SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary FROM employees;`
SUM() OVER()	Calculates cumulative sum	`SELECT name, salary, SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary FROM employees;`

Example: Calculating Running Totals

SELECT 
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales_data;

Example: Ranking by Department

SELECT 
  employee_id,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employee_salary;

Note: Window functions must be used with OVER() clause. The PARTITION BY clause is optional and defines groups within the result set.

10. Handling NULL Values

Function	Description	Example
ISNULL()	Returns 1 if the expression is NULL, otherwise 0	`SELECT ISNULL(column_name) FROM table_name;`
COALESCE()	Returns the first non-NULL value from a list of expressions	`SELECT COALESCE(column1, column2, 'default') FROM table_name;`
IFNULL()	MySQL-specific function; returns the second argument if the first is NULL	`SELECT IFNULL(column_name, 'N/A') FROM table_name;`
ISNULL() with CASE	Use CASE to handle NULLs in a more complex logic	`SELECT CASE WHEN column_name IS NULL THEN 'Missing' ELSE column_name END FROM table_name;`

Note: In Grafana, when querying databases with NULL values, it's important to handle them explicitly in your queries to avoid unexpected results or errors.

11. Optimizing Query Performance

Technique	Description	Impact
Use indexes	Ensure columns used in WHERE, JOIN, and ORDER BY clauses are indexed.	Significant performance improvement
Limit result sets	Use LIMIT to restrict the number of rows returned by queries.	Reduces memory usage and query time
Avoid SELECT *	Specify only required columns instead of retrieving all fields.	Improves speed and reduces bandwidth
Filter early	Apply WHERE conditions as early as possible in the query.	Reduces dataset size for further processing
Use EXPLAIN	Run EXPLAIN to analyze query execution plan and identify bottlenecks.	Helps understand how queries are executed

Example: Using LIMIT to optimize results

SELECT * FROM logs
WHERE timestamp >= '2024-01-01'
ORDER BY timestamp DESC
LIMIT 100;

Example: Filtering early to reduce dataset size

SELECT count(*) FROM events
WHERE event_type = 'user_login'
AND timestamp >= '2024-01-01'
AND status = 'success';

Note: Grafana's query editor can show query execution times, helping identify slow queries.

Key Tips for Query Optimization

Use indexes on frequently filtered columns
Apply filters at the earliest stage of query execution
Limit result sets to prevent excessive data transfer
Avoid selecting unnecessary columns
Use EXPLAIN to understand query plans

12. Using Query Inspector

Example: Inspecting a SQL Query in Grafana

SELECT 
  "timestamp",
  "metric_name",
  "value"
FROM 
  "metrics_table"
WHERE 
  "timestamp" >= $__timeFrom() 
  AND "timestamp" <= $__timeTo()
ORDER BY 
  "timestamp"
LIMIT 1000;

Feature	Description	Usage
Query Inspector	Tool to inspect and debug SQL queries in Grafana	Access via the "Inspect" button in the query editor
Query Execution Log	Shows the actual SQL query executed with parameters	View in "Query" tab of the inspector
Response Data	Raw data returned by the query	View in "Data" tab of the inspector
Query Analysis	Information about query performance and execution plan	View in "Query" tab of the inspector

Note: The Query Inspector is available in Grafana v9.0 and later.

Warning: Query Inspector may not show all query parameters for complex queries with multiple variables.

Key Features of Query Inspector

View raw SQL query with substituted variables
Inspect response data in JSON format
Analyze query execution time and performance
Debug issues with query results or formatting