For similar capabilities to Amazon Timestream for LiveAnalytics, consider Amazon Timestream for InfluxDB. It offers simplified data ingestion and single-digit millisecond query response times for real-time analytics. Learn more [here](https://docs.aws.amazon.com//timestream/latest/developerguide/timestream-for-influxdb.html).

# Sample queries
<a name="sample-queries"></a>

This section includes example use cases of Timestream for LiveAnalytics's query language.

**Topics**
+ [Simple queries](sample-queries.basic-scenarios.md)
+ [Queries with time series functions](sample-queries.devops-scenarios.md)
+ [Queries with aggregate functions](sample-queries.iot-scenarios.md)

# Simple queries
<a name="sample-queries.basic-scenarios"></a>

The following gets the 10 most recently added data points for a table.

```
SELECT * FROM <database_name>.<table_name>
ORDER BY time DESC
LIMIT 10
```

The following gets the 5 oldest data points for a specific measure.

```
SELECT * FROM <database_name>.<table_name>
WHERE measure_name = '<measure_name>'
ORDER BY time ASC
LIMIT 5
```

The following works with nanosecond granularity timestamps.

```
SELECT now() AS time_now
, now() - (INTERVAL '12' HOUR) AS twelve_hour_earlier -- Compatibility with ANSI SQL 
, now() - 12h AS also_twelve_hour_earlier -- Convenient time interval literals
, ago(12h) AS twelve_hours_ago -- More convenience with time functionality
, bin(now(), 10m) AS time_binned -- Convenient time binning support
, ago(50ns) AS fifty_ns_ago -- Nanosecond support
, now() + (1h + 50ns) AS hour_fifty_ns_future
```

Measure values for multi-measure records are identified by column name. Measure values for single-measure records are identified by `measure_value::<data_type>`, where `<data_type>` is one of `double`, `bigint`, `boolean`, or `varchar` as described in [Supported data types](supported-data-types.md). For more information about how measure values are modeled, see [Single table vs. multiple tables](https://docs.aws.amazon.com/timestream/latest/developerguide/data-modeling.html#data-modeling-multiVsinglerecords).

The following retrieves values for a measure called `speed` from multi-measure records with a `measure_name` of `IoTMulti-stats`.

```
SELECT speed FROM <database_name>.<table_name> where measure_name = 'IoTMulti-stats'
```

The following retrieves `double` values from single-measure records with a `measure_name` of `load`.

```
SELECT measure_value::double FROM <database_name>.<table_name> WHERE measure_name = 'load'
```

# Queries with time series functions
<a name="sample-queries.devops-scenarios"></a>

**Topics**
+ [Example dataset and queries](#sample-queries.devops-scenarios.example)

## Example dataset and queries
<a name="sample-queries.devops-scenarios.example"></a>

You can use Timestream for LiveAnalytics to understand and improve the performance and availability of your services and applications. Below is an example table and sample queries run on that table. 

The table `ec2_metrics` stores telemetry data, such as CPU utilization and other metrics from EC2 instances. You can view the table below.


| Time | region | az | Hostname | measure\$1name | measure\$1value::double | measure\$1value::bigint | 
| --- | --- | --- | --- | --- | --- | --- | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  cpu\$1utilization  |  35.1  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  memory\$1utilization  |  55.3  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  network\$1bytes\$1in  |  null  |  1,500  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  network\$1bytes\$1out  |  null  |  6,700  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  cpu\$1utilization  |  38.5  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  memory\$1utilization  |  58.4  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  network\$1bytes\$1in  |  null  |  23,000  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  network\$1bytes\$1out  |  null  |  12,000  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  cpu\$1utilization  |  45.0  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  memory\$1utilization  |  65.8  |  null  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  network\$1bytes\$1in  |  null  |  15,000  | 
|  2019-12-04 19:00:00.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  network\$1bytes\$1out  |  null  |  836,000  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  cpu\$1utilization  |  55.2  |  null  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  memory\$1utilization  |  75.0  |  null  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  network\$1bytes\$1in  |  null  |  1,245  | 
|  2019-12-04 19:00:05.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  network\$1bytes\$1out  |  null  |  68,432  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  cpu\$1utilization  |  65.6  |  null  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  memory\$1utilization  |  85.3  |  null  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  network\$1bytes\$1in  |  null  |  1,245  | 
|  2019-12-04 19:00:08.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  network\$1bytes\$1out  |  null  |  68,432  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  cpu\$1utilization  |  12.1  |  null  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  memory\$1utilization  |  32.0  |  null  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  network\$1bytes\$1in  |  null  |  1,400  | 
|  2019-12-04 19:00:20.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  network\$1bytes\$1out  |  null  |  345  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  cpu\$1utilization  |  15.3  |  null  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  memory\$1utilization  |  35.4  |  null  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  network\$1bytes\$1in  |  null  |  23  | 
|  2019-12-04 19:00:10.000000000  |  us-east-1  |  us-east-1a  |  frontend01  |  network\$1bytes\$1out  |  null  |  0  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  cpu\$1utilization  |  44.0  |  null  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  memory\$1utilization  |  64.2  |  null  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  network\$1bytes\$1in  |  null  |  1,450  | 
|  2019-12-04 19:00:16.000000000  |  us-east-1  |  us-east-1b  |  frontend02  |  network\$1bytes\$1out  |  null  |  200  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  cpu\$1utilization  |  66.4  |  null  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  memory\$1utilization  |  86.3  |  null  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  network\$1bytes\$1in  |  null  |  300  | 
|  2019-12-04 19:00:40.000000000  |  us-east-1  |  us-east-1c  |  frontend03  |  network\$1bytes\$1out  |  null  |  423  | 

Find the average, p90, p95, and p99 CPU utilization for a specific EC2 host over the past 2 hours:

```
SELECT region, az, hostname, BIN(time, 15s) AS binned_timestamp,
    ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.9), 2) AS p90_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.95), 2) AS p95_cpu_utilization,
    ROUND(APPROX_PERCENTILE(measure_value::double, 0.99), 2) AS p99_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
    AND hostname = 'host-Hovjv'
    AND time > ago(2h)
GROUP BY region, hostname, az, BIN(time, 15s)
ORDER BY binned_timestamp ASC
```

Identify EC2 hosts with CPU utilization that is higher by 10 % or more compared to the average CPU utilization of the entire fleet for the past 2 hours:

```
WITH avg_fleet_utilization AS (
    SELECT COUNT(DISTINCT hostname) AS total_host_count, AVG(measure_value::double) AS fleet_avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND time > ago(2h)
), avg_per_host_cpu AS (
    SELECT region, az, hostname, AVG(measure_value::double) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND time > ago(2h)
    GROUP BY region, az, hostname
)
SELECT region, az, hostname, avg_cpu_utilization, fleet_avg_cpu_utilization
FROM avg_fleet_utilization, avg_per_host_cpu
WHERE avg_cpu_utilization > 1.1 * fleet_avg_cpu_utilization
ORDER BY avg_cpu_utilization DESC
```

Find the average CPU utilization binned at 30 second intervals for a specific EC2 host over the past 2 hours:

```
SELECT BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
FROM "sampleDB".DevOps
WHERE measure_name = 'cpu_utilization'
    AND hostname = 'host-Hovjv'
    AND time > ago(2h)
GROUP BY hostname, BIN(time, 30s)
ORDER BY binned_timestamp ASC
```

Find the average CPU utilization binned at 30 second intervals for a specific EC2 host over the past 2 hours, filling in the missing values using linear interpolation:

```
WITH binned_timeseries AS (
    SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND hostname = 'host-Hovjv'
        AND time > ago(2h)
    GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
    SELECT hostname,
        INTERPOLATE_LINEAR(
            CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
                SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
    FROM binned_timeseries
    GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```

Find the average CPU utilization binned at 30 second intervals for a specific EC2 host over the past 2 hours, filling in the missing values using interpolation based on the last observation carried forward:

```
WITH binned_timeseries AS (
    SELECT hostname, BIN(time, 30s) AS binned_timestamp, ROUND(AVG(measure_value::double), 2) AS avg_cpu_utilization
    FROM "sampleDB".DevOps
    WHERE measure_name = 'cpu_utilization'
        AND hostname = 'host-Hovjv'
        AND time > ago(2h)
    GROUP BY hostname, BIN(time, 30s)
), interpolated_timeseries AS (
    SELECT hostname,
        INTERPOLATE_LOCF(
            CREATE_TIME_SERIES(binned_timestamp, avg_cpu_utilization),
                SEQUENCE(min(binned_timestamp), max(binned_timestamp), 15s)) AS interpolated_avg_cpu_utilization
    FROM binned_timeseries
    GROUP BY hostname
)
SELECT time, ROUND(value, 2) AS interpolated_cpu
FROM interpolated_timeseries
CROSS JOIN UNNEST(interpolated_avg_cpu_utilization)
```

# Queries with aggregate functions
<a name="sample-queries.iot-scenarios"></a>

Below is an example IoT scenario example data set to illustrate queries with aggregate functions.

**Topics**
+ [Example data](#sample-queries.iot-scenarios.example-data)
+ [Example queries](#sample-queries.iot-scenarios.example-queries)

## Example data
<a name="sample-queries.iot-scenarios.example-data"></a>

Timestream enables you to store and analyze IoT sensor data such as the location, fuel consumption, speed, and load capacity of one or more fleets of trucks to enable effective fleet management. Below is the schema and some of the data of a table iot\$1trucks that stores telemetry such as location, fuel consumption, speed, and load capacity of trucks.


| Time | truck\$1id | Make | Model | Fleet | fuel\$1capacity | load\$1capacity | measure\$1name | measure\$1value::double | measure\$1value::varchar | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | 
|  2019-12-04 19:00:00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  fuel\$1reading  |  65.2  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  load  |  400.0  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  speed  |  90.2  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456781  |  GMC  |  Astro  |  Alpha  |  100  |  500  |  location  |  null  |  47.6062 N, 122.3321 W  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  fuel\$1reading  |  10.1  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  load  |  950.3  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  speed  |  50.8  |  null  | 
|  2019-12-04 19:00:00.000000000  |  123456782  |  Kenworth  |  W900  |  Alpha  |  150  |  1000  |  location  |  null  |  40.7128 degrees N, 74.0060 degrees W  | 

## Example queries
<a name="sample-queries.iot-scenarios.example-queries"></a>

Get a list of all the sensor attributes and values being monitored for each truck in the fleet.

```
SELECT
    truck_id,
    fleet,
    fuel_capacity,
    model,
    load_capacity,
    make,
    measure_name
FROM "sampleDB".IoT
GROUP BY truck_id, fleet, fuel_capacity, model, load_capacity, make, measure_name
```

Get the most recent fuel reading of each truck in the fleet in the past 24 hours.

```
WITH latest_recorded_time AS (
    SELECT
        truck_id,
        max(time) as latest_time
    FROM "sampleDB".IoT
    WHERE measure_name = 'fuel-reading'
    AND time >= ago(24h)
    GROUP BY truck_id
)
SELECT
    b.truck_id,
    b.fleet,
    b.make,
    b.model,
    b.time,
    b.measure_value::double as last_reported_fuel_reading
FROM
latest_recorded_time a INNER JOIN "sampleDB".IoT b
ON a.truck_id = b.truck_id AND b.time = a.latest_time
WHERE b.measure_name = 'fuel-reading'
AND b.time > ago(24h)
ORDER BY b.truck_id
```

Identify trucks that have been running on low fuel(less than 10 %) in the past 48 hours:

```
WITH low_fuel_trucks AS (
    SELECT time, truck_id, fleet, make, model, (measure_value::double/cast(fuel_capacity as double)*100) AS fuel_pct
    FROM "sampleDB".IoT
    WHERE time >= ago(48h)
    AND (measure_value::double/cast(fuel_capacity as double)*100) < 10
    AND measure_name = 'fuel-reading'
),
other_trucks AS (
SELECT time, truck_id, (measure_value::double/cast(fuel_capacity as double)*100) as remaining_fuel
    FROM "sampleDB".IoT
    WHERE time >= ago(48h)
    AND truck_id IN (SELECT truck_id FROM low_fuel_trucks)
    AND (measure_value::double/cast(fuel_capacity as double)*100) >= 10
    AND measure_name = 'fuel-reading'
),
trucks_that_refuelled AS (
    SELECT a.truck_id
    FROM low_fuel_trucks a JOIN other_trucks b
    ON a.truck_id = b.truck_id AND b.time >= a.time
)
SELECT DISTINCT truck_id, fleet, make, model, fuel_pct
FROM low_fuel_trucks
WHERE truck_id NOT IN (
    SELECT truck_id FROM trucks_that_refuelled
)
```

Find the average load and max speed for each truck for the past week:

```
SELECT
    bin(time, 1d) as binned_time,
    fleet,
    truck_id,
    make,
    model,
    AVG(
        CASE WHEN measure_name = 'load' THEN measure_value::double ELSE NULL END
    ) AS avg_load_tons,
    MAX(
        CASE WHEN measure_name = 'speed' THEN measure_value::double ELSE NULL END
    ) AS max_speed_mph
FROM "sampleDB".IoT
WHERE time >= ago(7d)
AND measure_name IN ('load', 'speed')
GROUP BY fleet, truck_id, make, model, bin(time, 1d)
ORDER BY truck_id
```

Get the load efficiency for each truck for the past week:

```
WITH average_load_per_truck AS (
    SELECT
        truck_id,
        avg(measure_value::double)  AS avg_load
    FROM "sampleDB".IoT
    WHERE measure_name = 'load'
    AND time >= ago(7d)
    GROUP BY truck_id, fleet, load_capacity, make, model
),
truck_load_efficiency AS (
    SELECT
        a.truck_id,
        fleet,
        load_capacity,
        make,
        model,
        avg_load,
        measure_value::double,
        time,
        (measure_value::double*100)/avg_load as load_efficiency -- , approx_percentile(avg_load_pct, DOUBLE '0.9')
    FROM "sampleDB".IoT a JOIN average_load_per_truck b
    ON a.truck_id = b.truck_id
    WHERE a.measure_name = 'load'
)
SELECT
    truck_id,
    time,
    load_efficiency
FROM truck_load_efficiency
ORDER BY truck_id, time
```