Time series data is a series of data points each associated with a specific time. Examples include:
- Server performance metrics
- Financial averages over time
- Sensor data, such as temperature, barometric pressure, wind speeds, etc.
Relational databases can be used to store and analyze time series data, but depending on the precision of your data, a query can involve potentially millions of rows. InfluxDB is purpose-built to store and query data by time, providing out-of-the-box functionality that optionally downsamples data after a specific age and a query engine optimized for time-based data.
2.1 database & duration
A logical container for users, retention policies, continuous queries, and time series data.
The attribute of the retention policy that determines how long InfluxDB stores data. Data older than the duration are automatically dropped from the database.
The key-value pair in an InfluxDB data structure that records metadata and the actual data value. Fields are required in InfluxDB data structures and they are not indexed - queries on field values scan all points that match the specified time range and, as a result, are not performant relative to tags.
Field keys are strings and they store metadata.Field values are the actual data; they can be strings, floats, integers, or booleans. A field value is always associated with a timestamp.
Tags are optional. The key-value pair in the InfluxDB data structure that records metadata.You don’t need to have tags in your data structure, but it’s generally a good idea to make use of them because, unlike fields, tags are indexed. This means that queries on tags are faster and that tags are ideal for storing commonly-queried metadata.
Tags 與 fields 的區別
Tags are indexed and fields are not indexed. This means that queries on tags are more performant than those on fields.
Tags 與 fields 的使用場景
（1）Store commonly-queried meta data in tags
（2）Store data in tags if you plan to use them with the InfluxQL
GROUP BY clause
（3）Store data in fields if you plan to use them with an InfluxQL function
（4）Store numeric values as fields (tag values only support string values)
The measurement acts as a container for tags, fields, and the
time column, and the measurement name is the description of the data that are stored in the associated fields. Measurement names are strings, and, for any SQL users out there, a measurement is conceptually similar to a table.
In InfluxDB, a point represents a single data record, similar to a row in a SQL database table. Each point:
- has a measurement, a tag set, a field key, a field value, and a timestamp;
- is uniquely identified by its series and timestamp.
You cannot store more than one point with the same timestamp in a series. If you write a point to a series with a timestamp that matches an existing point, the field set becomes a union of the old and new field set, and any ties go to the new field set.
In InfluxDB, a series is a collection of points that share a measurement, tag set, and field key. A point represents a single data record that has four components: a measurement, tag set, field set, and a timestamp. A point is uniquely identified by its series and timestamp.
A series key identifies a particular series by measurement, tag set, and field key.
select fieldName from measurementName where fieldName=~/^給定欄位/
select fieldName from measurementName where fieldName=~/給定欄位$/
select fieldName from measurementName where fieldName=~/給定欄位/
3.2 Select 注意事項:
A query requires at least one field key in the
SELECT clause to return data. If the
SELECT clause only includes a single tag key or several tag keys, the query returns an empty response. This behavior is a result of how the system stores data.
3.3 Where 限定
（1）Single quote string field values in the
WHERE clause. Queries with unquoted string field values or double quoted string field values will not return any data and, in most cases,will not return an error.
（2）Single quote tag values in the
WHERE clause. Queries with unquoted tag values or double quoted tag values will not return any data and, in most cases, will not return an error.
3.4 Group By
（1）Note that the
GROUP BY clause must come after the
GROUP BY clause groups query results by: one or more specified tags ；specified time interval。
（3）You cannot use
GROUP BY to group fields.
fill() changes the value reported for time intervals that have no data.
By default, a
GROUP BY time() interval with no data reports
null as its value in the output column.
fill() changes the value reported for time intervals that have no data. Note that
fill() must go at the end of the
GROUP BY clause if you’re
GROUP(ing) BY several things (for example, both tags and a time interval).
3.5 ORDER BY time DESC
By default, InfluxDB returns results in ascending time order; the first point returned has the oldest timestamp and the last point returned has the most recent timestamp.
ORDER BY time DESC reverses that order such that InfluxDB returns the points with the most recent timestamps first.
ORDER by time DESC must appear after the
GROUP BY clause if the query includes a
GROUP BY clause.
ORDER by time DESC must appear after the
WHERE clause if the query includes a
WHERE clause and no
GROUP BY clause.
是用於估計或精確計算measurement、序列、tag key、tag value和field key的基數的一組命令。
SHOW CARDINALITY命令有兩種可用的版本：估計和精確。估計值使用草圖進行計算，對於所有基數大小來說，這是一個安全預設值。精確值是直接對TSM（Time-Structured Merge Tree）資料進行計數，但是，對於基數大的資料來說，執行成本很高。
下面以tag key、tag value為例。
4.1 SHOW TAG KEY CARDINALITY
ON <database>、FROM <sources>、WITH KEY = <key>、WHERE <condition>、GROUP BY <dimensions>和LIMIT/OFFSET子句是可選的。當使用這些查詢子句時，查詢將回退到精確計數（exect count）。當啟用Time Series Index（TSI）時，才支援對time進行過濾。不支援在WHERE子句中使用time。
-- show estimated tag key cardinality SHOW TAG KEY CARDINALITY
----計算精確值 -- show exact tag key cardinality SHOW TAG KEY EXACT CARDINALITY
4.2 SHOW TAG VALUES CARDINALITY
估計或精確計算指定tag key對應的tag value的基數。
ON <database>、FROM <sources>、WITH KEY = <key>、WHERE <condition>、GROUP BY <dimensions>和LIMIT/OFFSET子句是可選的。當使用這些查詢子句時，查詢將回退到精確計數（exect count）。當啟用Time Series Index（TSI）時，才支援對time進行過濾。
-- show estimated tag key values cardinality for a specified tag key SHOW TAG VALUES CARDINALITY WITH KEY = "myTagKey" -- show estimated tag key values cardinality for a specified tag key SHOW TAG VALUES CARDINALITY WITH KEY = "myTagKey"
-- show exact tag key values cardinality for a specified tag key SHOW TAG VALUES EXACT CARDINALITY WITH KEY = "myTagKey" -- show exact tag key values cardinality for a specified tag key SHOW TAG VALUES EXACT CARDINALITY WITH KEY = "myTagKey"
例如，前面的分享，我們通過Telegraf 將server的監控資料儲存到了InfluxDB中，其中CPU指標是必不可少的（telegraf.conf 設定）。假如有一天，我們需要統計telegraf一共部署了多少臺。其實就可以通過SHOW TAG VALUES EXACT CARDINALITY 獲得。
SHOW TAG VALUES EXACT CARDINALITY from "cpu" WITH KEY = "host"
即檢視cpu 中 host 的key值有多少個。因為通過telegraf.conf的設定，一臺Server 對應一個唯一的host值，host值有多少個，就有多少臺Server已部署了telegraf。
5 Drop 與 Delete
DROP SERIES query deletes all points from a series in a database, and it drops the series from the index.
The query takes the following form, where you must specify either the
FROM clause or the
DROP SERIES FROM <measurement_name[,measurement_name]> WHERE <tag_key>='<tag_value>'
DROP SERIES query returns an empty result.
Drop all points in the series that have a specific tag pair from all measurements in the database(即，如不指定from，將會把符合條件的所有表tag資料刪除).
與Delete series 的區別是：
DELETE query deletes all points from a series in a database. Unlike
DELETE does not drop the series from the index.
DELETE FROM <measurement_name> WHERE [<tag_key>='<tag_value>'] | [<time interval>]
measurement的drop,是比較消耗資源的，並且操作時間相對較長。看有網友的分享，建議 在 drop measurement 之前先刪除所有的 tag。
DROP SERIES FROM 'measurement_name'
drop measurement <measurement_name>
|聚合類||COUNT()||Returns the number of non-null field values.|
|DISTINCT()||Returns the list of unique field values.||
|INTEGRAL()||Returns the area under the curve for subsequent field values.||InfluxDB calculates the area under the curve for subsequent field values and converts those results into the summed area per
|MEAN()||Returns the arithmetic mean (average) of field values.|
|MEDIAN()||Returns the middle value from a sorted list of field values.||
|MODE()||Returns the most frequent value in a list of field values.||
|SPREAD()||Returns the difference between the minimum and maximum field values.|
|STDDEV()||Returns the standard deviation of field values.|
|SUM()||Returns the sum of field values.|
|查詢選擇類||BOTTOM()||Returns the smallest
|FIRST()||Returns the field value with the oldest timestamp.|
|LAST()||Returns the field value with the most recent timestamp.|
|MAX()||Returns the greatest field value.|
|MIN()||Returns the lowest field value.|
|SAMPLE()||Returns a random sample of
|TOP()||Returns the greatest
|轉換類||ABS()||Returns the absolute value of the field value.|
|ACOS()||Returns the arccosine (in radians) of the field value.||Field values must be between -1 and 1.|
|ASIN()||Returns the arcsine (in radians) of the field value.||Field values must be between -1 and 1.|
|ATAN()||Returns the arctangent (in radians) of the field value.||Field values must be between -1 and 1.|
|ATAN2()||Returns the the arctangent of
|CEIL()||Returns the subsequent value rounded up to the nearest integer.|
|COS()||Returns the cosine of the field value.|
|CUMULATIVE_SUM()||Returns the running total of subsequent field values.|
|DERIVATIVE()||Returns the rate of change between subsequent field values.||InfluxDB calculates the difference between subsequent field values and converts those results into the rate of change per
|DIFFERENCE()||Returns the result of subtraction between subsequent field values.|
|ELAPSED()||Returns the difference between subsequent field value’s timestamps.||InfluxDB calculates the difference between subsequent timestamps. The
|EXP()||Returns the exponential of the field value.|
|FLOOR()||Returns the subsequent value rounded down to the nearest integer.|
|LN()||Returns the natural logarithm of the field value.|
|LOG()||Returns the logarithm of the field value with base
|LOG2()||Returns the logarithm of the field value to the base 2.|
|LOG10()||Returns the logarithm of the field value to the base 10.|
|MOVING_AVERAGE()||Returns the rolling average across a window of subsequent field values.|
|POW()||Returns the field value to the power of
|ROUND()||Returns the subsequent value rounded to the nearest integer.|
|SIN()||Returns the sine of the field value.|
|SQRT()||Returns the square root of field value.|
|TAN()||Returns the tangent of the field value.|
|推測類||HOLT_WINTERS()||Returns N number of predicted field values||
Predict when data values will cross a given threshold；
Compare predicted values with actual values to detect anomalies in your data.
|技術分析類||CHANDE_MOMENTUM_OSCILLATOR()||The Chande Momentum Oscillator (CMO) is a technical momentum indicator developed by Tushar Chande. The CMO indicator is created by calculating the difference between the sum of all recent higher data points and the sum of all recent lower data points, then dividing the result by the sum of all data movement over a given time period. The result is multiplied by 100 to give the -100 to +100 range.|
|EXPONENTIAL_MOVING_AVERAGE()||An exponential moving average (EMA) is a type of moving average that is similar to a simple moving average, except that more weight is given to the latest data. It’s also known as the “exponentially weighted moving average.” This type of moving average reacts faster to recent data changes than a simple moving average.|
|DOUBLE_EXPONENTIAL_MOVING_AVERAGE()||The Double Exponential Moving Average (DEMA) attempts to remove the inherent lag associated to Moving Averages by placing more weight on recent values. The name suggests this is achieved by applying a double exponential smoothing which is not the case. The name double comes from the fact that the value of an EMA is doubled. To keep it in line with the actual data and to remove the lag, the value “EMA of EMA” is subtracted from the previously doubled EMA.|
|KAUFMANS_EFFICIENCY_RATIO()||Kaufman’s Efficiency Ration, or simply “Efficiency Ratio” (ER), is calculated by dividing the data change over a period by the absolute sum of the data movements that occurred to achieve that change. The resulting ratio ranges between 0 and 1 with higher values representing a more efficient or trending market.
The ER is very similar to the Chande Momentum Oscillator (CMO). The difference is that the CMO takes market direction into account, but if you take the absolute CMO and divide by 100, you you get the Efficiency Ratio.
|KAUFMANS_ADAPTIVE_MOVING_AVERAGE()||Kaufman’s Adaptive Moving Average (KAMA) is a moving average designed to account for sample noise or volatility. KAMA will closely follow data points when the data swings are relatively small and noise is low. KAMA will adjust when the data swings widen and follow data from a greater distance. This trend-following indicator can be used to identify the overall trend, time turning points and filter data movements.|
|TRIPLE_EXPONENTIAL_MOVING_AVERAGE()||The triple exponential moving average (TEMA) was developed to filter out volatility from conventional moving averages. While the name implies that it’s a triple exponential smoothing, it’s actually a composite of a single exponential moving average, a double exponential moving average, and a triple exponential moving average.|
|TRIPLE_EXPONENTIAL_DERIVATIVE()||The triple exponential derivative indicator, commonly referred to as “TRIX,” is an oscillator used to identify oversold and overbought markets, and can also be used as a momentum indicator. TRIX calculates a triple exponential moving average of the log of the data input over the period of time. The previous value is subtracted from the previous value. This prevents cycles that are shorter than the defined period from being considered by the indicator.
Like many oscillators, TRIX oscillates around a zero line. When used as an oscillator, a positive value indicates an overbought market while a negative value indicates an oversold market. When used as a momentum indicator, a positive value suggests momentum is increasing while a negative value suggests momentum is decreasing. Many analysts believe that when the TRIX crosses above the zero line it gives a buy signal, and when it closes below the zero line, it gives a sell signal.
|RELATIVE_STRENGTH_INDEX()||The relative strength index (RSI) is a momentum indicator that compares the magnitude of recent increases and decreases over a specified time period to measure speed and change of data movements.|