You need to sign in or sign up before continuing.
10-function.md 40.6 KB
Newer Older
1
---
2
title: Functions
D
danielclow 已提交
3 4
sidebar_label: Functions
description: This document describes the standard SQL functions available in TDengine.
5
toc_max_heading_level: 4
6 7
---

8
## Single Row Functions
9

10
Single row functions return a result for each row.
11

12
### Mathematical Functions
13

14
#### ABS
15

16
```sql
G
Ganlin Zhao 已提交
17
ABS(expr)
18
```
19

20
**Description**: The absolute value of a specific field.
21

22
**Return value type**: Same as the field being used
23

24
**Applicable data types**: Numeric
25

26
**Nested query**: It can be used in both the outer query and inner query in a nested query.
27

28
**Applicable table types**: standard tables and supertables
29

30
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
31

32
#### ACOS
33

34
```sql
G
Ganlin Zhao 已提交
35
ACOS(expr)
36 37
```

38
**Description**: The arc cosine of a specific field.
39

40
**Return value type**: Double
41

42
**Applicable data types**: Numeric
43

44
**Nested query**: It can be used in both the outer query and inner query in a nested query.
45

46
**Applicable table types**: standard tables and supertables
47

48
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
49

50
#### ASIN
51

52
```sql
G
Ganlin Zhao 已提交
53
ASIN(expr)
54 55
```

56
**Description**: The arc sine of a specific field.
57

58
**Return value type**: Double
59

60
**Applicable data types**: Numeric
61

62
**Nested query**: It can be used in both the outer query and inner query in a nested query.
63

64 65 66
**Applicable table types**: standard tables and supertables

**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
67

68

69
#### ATAN
70

71
```sql
G
Ganlin Zhao 已提交
72
ATAN(expr)
73 74
```

75
**Description**: The arc tangent of a specific field.
76

77
**Return value type**: Double
78

79
**Applicable data types**: Numeric
80

81
**Nested query**: It can be used in both the outer query and inner query in a nested query.
82

83 84 85
**Applicable table types**: standard tables and supertables

**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
86

87

88
#### CEIL
89

90
```sql
G
Ganlin Zhao 已提交
91
CEIL(expr)
92 93
```

94
**Description**: The rounded up value of a specific field
95

96
**Return value type**: Same as the field being used
97

98
**Applicable data types**: Numeric
99

100
**Applicable table types**: standard tables and supertables
101

102
**Nested query**: It can be used in both the outer query and inner query in a nested query.
103

104
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
105

106
#### COS
107

108
```sql
G
Ganlin Zhao 已提交
109
COS(expr)
110 111
```

112
**Description**: The cosine of a specific field.
113

114
**Return value type**: Double
115

116
**Applicable data types**: Numeric
117

118
**Nested query**: It can be used in both the outer query and inner query in a nested query.
119

120
**Applicable table types**: standard tables and supertables
121

122
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
123

124
#### FLOOR
125

126
```sql
G
Ganlin Zhao 已提交
127
FLOOR(expr)
128 129
```

G
Ganlin Zhao 已提交
130
**Description**: The rounded down value of a specific field
131
 **More explanations**: The restrictions are same as those of the `CEIL` function.
132 133 134 135

#### LOG

```sql
G
Ganlin Zhao 已提交
136
LOG(expr [, base])
137 138
```

139 140 141
**Description**: The logarithm of a specific field with `base` as the radix. If you do not enter a base, the natural logarithm of the field is returned.

**Return value type**: Double
142

143
**Applicable data types**: Numeric
144

145
**Nested query**: It can be used in both the outer query and inner query in a nested query.
146

147
**Applicable table types**: standard tables and supertables
148

149
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
150 151


152
#### POW
153

154
```sql
G
Ganlin Zhao 已提交
155
POW(expr, power)
156
```
157

158 159 160
**Description**: The power of a specific field with `power` as the exponent.

**Return value type**: Double
161

162
**Applicable data types**: Numeric
163

164
**Nested query**: It can be used in both the outer query and inner query in a nested query.
165

166
**Applicable table types**: standard tables and supertables
167

168
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
169 170


171
#### ROUND
172

173
```sql
G
Ganlin Zhao 已提交
174
ROUND(expr)
175 176
```

G
Ganlin Zhao 已提交
177
**Description**: The rounded value of a specific field.
178
 **More explanations**: The restrictions are same as those of the `CEIL` function.
179 180


181
#### SIN
182

183
```sql
G
Ganlin Zhao 已提交
184
SIN(expr)
185
```
186

187
**Description**: The sine of a specific field.
188

189
**Return value type**: Double
190

191
**Applicable data types**: Numeric
192

193
**Nested query**: It can be used in both the outer query and inner query in a nested query.
194

195
**Applicable table types**: standard tables and supertables
196

197
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
198

199
#### SQRT
200

201
```sql
G
Ganlin Zhao 已提交
202
SQRT(expr)
203
```
204

205
**Description**: The square root of a specific field.
206

207
**Return value type**: Double
208

209
**Applicable data types**: Numeric
G
gccgdb1234 已提交
210

211
**Nested query**: It can be used in both the outer query and inner query in a nested query.
G
gccgdb1234 已提交
212

213
**Applicable table types**: standard tables and supertables
214

215
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
G
gccgdb1234 已提交
216

217
#### TAN
218

219
```sql
G
Ganlin Zhao 已提交
220
TAN(expr)
221 222
```

223
**Description**: The tangent of a specific field.
G
gccgdb1234 已提交
224

225
**Return value type**: Double
G
gccgdb1234 已提交
226

227
**Applicable data types**: Numeric
G
gccgdb1234 已提交
228

229
**Nested query**: It can be used in both the outer query and inner query in a nested query.
G
gccgdb1234 已提交
230

231
**Applicable table types**: standard tables and supertables
G
gccgdb1234 已提交
232

233
**Usage**: This function can only be used on data columns. It can be used with selection and projection functions but not with aggregation functions.
G
gccgdb1234 已提交
234

235
### Concatenation Functions
236

237
Concatenation functions take strings as input and produce string or numeric values as output.
238

239
#### CHAR_LENGTH
240

241
```sql
G
Ganlin Zhao 已提交
242
CHAR_LENGTH(expr)
243 244
```

245
**Description**: The length in number of characters of a string
246

247
**Return value type**: Bigint
248

249
**Applicable data types**: VARCHAR and NCHAR
250

251
**Nested query**: It can be used in both the outer query and inner query in a nested query.
252

253
**Applicable table types**: standard tables and supertables
254

255
#### CONCAT
256

257
```sql
G
Ganlin Zhao 已提交
258
CONCAT(expr1, expr2 [, expr] ...)
259 260
```

261
**Description**: The concatenation result of two or more strings
262

263
**Return value type**: If the concatenated strings are VARCHARs, the result is a VARCHAR. If the concatenated strings are NCHARs, the result is an NCHAR. If an input value is null, the result is null.
264

265
**Applicable data types**: VARCHAR and NCHAR You can concatenate between 2 and 8 strings.
266

267
**Nested query**: It can be used in both the outer query and inner query in a nested query.
268

269
**Applicable table types**: standard tables and supertables
270 271


272
#### CONCAT_WS
273

274
```sql
G
Ganlin Zhao 已提交
275
CONCAT_WS(separator_expr, expr1, expr2 [, expr] ...)
276 277
```

278
**Description**: The concatenation result of two or more strings with separator
279

280
**Return value type**: If the concatenated strings are VARCHARs, the result is a VARCHAR. If the concatenated strings are NCHARs, the result is an NCHAR. If an input value is null, the result is null.
281

282
**Applicable data types**: VARCHAR and NCHAR You can concatenate between 3 and 9 strings.
283

284
**Nested query**: It can be used in both the outer query and inner query in a nested query.
285

286
**Applicable table types**: standard tables and supertables
287 288


289
#### LENGTH
290

291
```sql
G
Ganlin Zhao 已提交
292
LENGTH(expr)
293 294
```

295
**Description**: The length in bytes of a string
296

297
**Return value type**: Bigint
298

299
**Applicable data types**: VARCHAR and NCHAR fields or columns
300

301
**Nested query**: It can be used in both the outer query and inner query in a nested query.
302

303
**Applicable table types**: standard tables and supertables
304 305


306
#### LOWER
307

308
```sql
G
Ganlin Zhao 已提交
309
LOWER(expr)
310 311
```

312
**Description**: Convert the input string to lower case
313

314
**Return value type**: Same as input
315

316
**Applicable data types**: VARCHAR and NCHAR
317

318
**Nested query**: It can be used in both the outer query and inner query in a nested query.
319

320
**Applicable table types**: standard tables and supertables
321 322


323
#### LTRIM
324

325
```sql
G
Ganlin Zhao 已提交
326
LTRIM(expr)
327 328
```

329
**Description**: Remove the left leading blanks of a string
330

331
**Return value type**: Same as input
332

333
**Applicable data types**: VARCHAR and NCHAR
334

335
**Nested query**: It can be used in both the outer query and inner query in a nested query.
336

337
**Applicable table types**: standard tables and supertables
338 339


340
#### RTRIM
341

342
```sql
G
Ganlin Zhao 已提交
343
LTRIM(expr)
344 345
```

346
**Description**: Remove the right tailing blanks of a string
347

348
**Return value type**: Same as input
349

350
**Applicable data types**: VARCHAR and NCHAR
351

352
**Nested query**: It can be used in both the outer query and inner query in a nested query.
353

354
**Applicable table types**: standard tables and supertables
355 356


357
#### SUBSTR
358

359
```sql
G
Ganlin Zhao 已提交
360
SUBSTR(expr, pos [, len])
361 362
```

363
**Description**: The sub-string starting from `pos` with length of `len` from the original string `str` - If `len` is not specified, it means from `pos` to the end.
364

365
**Return value type**: Same as input
366

367
**Applicable data types**: VARCHAR and NCHAR Parameter `pos` can be an positive or negative integer; If it's positive, the starting position will be counted from the beginning of the string; if it's negative, the starting position will be counted from the end of the string.
368

369
**Nested query**: It can be used in both the outer query and inner query in a nested query.
370

371
**Applicable table types**: table, STable
372 373


374
#### UPPER
375

376
```sql
G
Ganlin Zhao 已提交
377
UPPER(expr)
378 379
```

380
**Description**: Convert the input string to upper case
381

382
**Return value type**: Same as input
383

384
**Applicable data types**: VARCHAR and NCHAR
385

386
**Nested query**: It can be used in both the outer query and inner query in a nested query.
387

388
**Applicable table types**: table, STable
389 390


391
### Conversion Functions
392

393
Conversion functions change the data type of a value.
394

395
#### CAST
396

397
```sql
G
Ganlin Zhao 已提交
398
CAST(expr AS type_name)
399 400
```

G
Ganlin Zhao 已提交
401
**Description**: Convert the input data `expr` into the type specified by `type_name`. This function can be used only in SELECT statements.
402

403 404 405 406 407
**Return value type**: The type specified by parameter `type_name`

**Applicable data types**: All data types except JSON

**Nested query**: It can be used in both the outer query and inner query in a nested query.
408

409
**Applicable table types**: standard tables and supertables
410

411
**More explanations**:
412

413
- Error will be reported for unsupported type casting
414
- Some values of some supported data types may not be casted, below are known issues:
415 416 417
        1. Some strings cannot be converted to numeric values. For example, the string `a` may be converted to `0`. However, this does not produce an error.
        2. If a converted numeric value is larger than the maximum size for the specified type, an overflow will occur. However, this does not produce an error.
        3. If a converted string value is larger than the maximum size for the specified type, the output value will be truncated. However, this does not produce an error.
418

419
#### TO_ISO8601
420

421
```sql
G
Ganlin Zhao 已提交
422
TO_ISO8601(expr [, timezone])
423 424
```

425
**Description**: The ISO8601 date/time format converted from a UNIX timestamp, plus the timezone. You can specify any time zone with the timezone parameter. If you do not enter this parameter, the time zone on the client is used.
426

427
**Return value type**: VARCHAR
428

429
**Applicable data types**: Integers and timestamps
430

431 432 433
**Nested query**: It can be used in both the outer query and inner query in a nested query.

**Applicable table types**: standard tables and supertables
434

435
**More explanations**:
436

437
- You can specify a time zone in the following format: [z/Z, +/-hhmm, +/-hh, +/-hh:mm]。 For example, TO_ISO8601(1, "+00:00").
G
Ganlin Zhao 已提交
438
- If the input is a UNIX timestamp, the precision of the returned value is determined by the digits of the input timestamp
439 440
- If the input is a column of TIMESTAMP type, the precision of the returned value is same as the precision set for the current data base in use

441

442
#### TO_JSON
443

444
```sql
G
Ganlin Zhao 已提交
445
TO_JSON(str_literal)
446 447
```

448
**Description**: Converts a string into JSON.
449

450
**Return value type**: JSON
451

452
**Applicable data types**: JSON strings in the form `{"literal": literal}`. `{}` indicates a null value. The key must be a string literal. The value can be a numeric literal, string literal, Boolean literal, or null literal. str_literal cannot include escape characters.
453

454 455 456
**Nested query**: It can be used in both the outer query and inner query in a nested query.

**Applicable table types**: table, STable
457 458


459
#### TO_UNIXTIMESTAMP
460

461
```sql
G
Ganlin Zhao 已提交
462 463 464 465 466 467
TO_UNIXTIMESTAMP(expr [, return_timestamp])

return_timestamp: {
    0
  | 1
}
468 469
```

470
**Description**: UNIX timestamp converted from a string of date/time format
471

G
Ganlin Zhao 已提交
472
**Return value type**: BIGINT, TIMESTAMP
473

474
**Applicable column types**: VARCHAR and NCHAR
475

476 477 478
**Nested query**: It can be used in both the outer query and inner query in a nested query.

**Applicable table types**: standard tables and supertables
479

480
**More explanations**:
481

482 483
- The input string must be compatible with ISO8601/RFC3339 standard, NULL will be returned if the string can't be converted
- The precision of the returned timestamp is same as the precision set for the current data base in use
G
Ganlin Zhao 已提交
484
- return_timestamp indicates whether the returned value type is TIMESTAMP or not. If this parameter set to 1, function will return TIMESTAMP type. Otherwise function will return BIGINT type. If parameter is omitted, default return value type is BIGINT.
485 486 487


### Time and Date Functions
488

489
These functions perform operations on times and dates.
490

491
All functions that return the current time, such as `NOW`, `TODAY`, and `TIMEZONE`, are calculated only once per statement even if they appear multiple times.
492

493
#### NOW
494

495
```sql
G
Ganlin Zhao 已提交
496
NOW()
497
```
498

499
**Description**: The current time of the client side system
500

501
**Return value type**: TIMESTAMP
502

503
**Applicable column types**: TIMESTAMP only
504

505 506 507
**Applicable table types**: standard tables and supertables

**Nested query**: It can be used in both the outer query and inner query in a nested query.
508

509
**More explanations**:
510

511 512 513 514
- Add and Subtract operation can be performed, for example NOW() + 1s, the time unit can be:
        b(nanosecond), u(microsecond), a(millisecond)), s(second), m(minute), h(hour), d(day), w(week)
- The precision of the returned timestamp is same as the precision set for the current data base in use

515

516 517 518
#### TIMEDIFF

```sql
G
Ganlin Zhao 已提交
519
TIMEDIFF(expr1, expr2 [, time_unit])
520 521
```

522
**Description**: The difference between two timestamps, and rounded to the time unit specified by `time_unit`
523

524
**Return value type**: BIGINT
525

526
**Applicable column types**: UNIX-style timestamps in BIGINT and TIMESTAMP format and other timestamps in VARCHAR and NCHAR format
527

528
**Applicable table types**: standard tables and supertables
529

530
**Nested query**: It can be used in both the outer query and inner query in a nested query.
531

532
**More explanations**:
533
- Time unit specified by `time_unit` can be:
534 535 536 537
          1b (nanoseconds), 1u (microseconds), 1a (milliseconds), 1s (seconds), 1m (minutes), 1h (hours), 1d (days), or 1w (weeks)
- The precision of the returned timestamp is same as the precision set for the current data base in use
- If the input data is not formatted as a timestamp, the returned value is null.

538

539
#### TIMETRUNCATE
540 541

```sql
542 543 544 545 546 547
TIMETRUNCATE(expr, time_unit [, ignore_timezone])

ignore_timezone: {
    0
  | 1
}
548 549
```

550
**Description**: Truncate the input timestamp with unit specified by `time_unit`
551

552
**Return value type**: TIMESTAMP
553

554
**Applicable column types**: UNIX-style timestamps in BIGINT and TIMESTAMP format and other timestamps in VARCHAR and NCHAR format
555

556
**Applicable table types**: standard tables and supertables
557 558

**More explanations**:
559
- Time unit specified by `time_unit` can be:
560 561 562
          1b (nanoseconds), 1u (microseconds), 1a (milliseconds), 1s (seconds), 1m (minutes), 1h (hours), 1d (days), or 1w (weeks)
- The precision of the returned timestamp is same as the precision set for the current data base in use
- If the input data is not formatted as a timestamp, the returned value is null.
563
- If `1d` is used as `time_unit` to truncate the timestamp, `ignore_timezone` option can be set to indicate if the returned result is affected by client timezone or not.
564 565 566
  For example, if client timezone is set to UTC+0800, TIMETRUNCATE('2020-01-01 23:00:00', 1d, 0) will return '2020-01-01 08:00:00'.
  Otherwise, TIMETRUNCATE('2020-01-01 23:00:00', 1d, 1) will return '2020-01-01 00:00:00'.
  If `ignore_timezone` option is omitted, the default value is set to 1.
567

568
#### TIMEZONE
569 570

```sql
G
Ganlin Zhao 已提交
571
TIMEZONE()
572 573
```

574
**Description**: The timezone of the client side system
575

576
**Applicable data types**: VARCHAR
577

578 579 580
**Applicable column types**: None

**Applicable table types**: standard tables and supertables
581 582


583
#### TODAY
584

585
```sql
G
Ganlin Zhao 已提交
586
TODAY()
587 588
```

589
**Description**: The timestamp of 00:00:00 of the client side system
590

591
**Return value type**: TIMESTAMP
592

593
**Applicable column types**: TIMESTAMP only
594

595
**Applicable table types**: standard tables and supertables
596

597
**More explanations**:
598

599 600 601 602
- Add and Subtract operation can be performed, for example TODAY() + 1s, the time unit can be:
                b(nanosecond), u(microsecond), a(millisecond)), s(second), m(minute), h(hour), d(day), w(week)
- The precision of the returned timestamp is same as the precision set for the current data base in use

603

604
## Aggregate Functions
605

606 607 608
Aggregate functions return one row per group. You can use windows or GROUP BY to group data. Otherwise, the entire query is considered a single group.

TDengine supports the following aggregate functions:
609

G
Ganlin Zhao 已提交
610 611
### APERCENTILE

612
```sql
G
Ganlin Zhao 已提交
613 614 615 616 617 618
APERCENTILE(expr, p [, algo_type])

algo_type: {
    "default"
  | "t-digest"
}
G
Ganlin Zhao 已提交
619 620
```

621
**Description**: Similar to `PERCENTILE`, but a simulated result is returned
G
Ganlin Zhao 已提交
622

623
**Return value type**: DOUBLE
G
Ganlin Zhao 已提交
624

625
**Applicable data types**: Numeric
G
Ganlin Zhao 已提交
626

627
**Applicable table types**: standard tables and supertables
G
Ganlin Zhao 已提交
628

629
**Explanations**
G
Ganlin Zhao 已提交
630
- _p_ is in range [0,100], when _p_ is 0, the result is same as using function MIN; when _p_ is 100, the result is same as function MAX.
631
- `algo_type` can only be input as `default` or `t-digest` Enter `default` to use a histogram-based algorithm. Enter `t-digest` to use the t-digest algorithm to calculate the approximation of the quantile. `default` is used by default.
632
- The approximation result of `t-digest` algorithm is sensitive to input data order. For example, when querying STable with different input data order there might be minor differences in calculated results.
G
Ganlin Zhao 已提交
633

634
### AVG
635

636
```sql
G
Ganlin Zhao 已提交
637
AVG(expr)
638 639
```

640 641 642
**Description**: The average value of the specified fields.

**Return value type**: DOUBLE
643

644
**Applicable data types**: Numeric
645

646
**Applicable table types**: standard tables and supertables
647 648


649
### COUNT
650

651
```sql
G
Ganlin Zhao 已提交
652
COUNT({* | expr})
653 654
```

655
**Description**: The number of records in the specified fields.
656

657
**Return value type**: BIGINT
658

659
**Applicable data types**: Numeric
660

661
**Applicable table types**: standard tables and supertables
662

663
**More explanation**:
664

665 666 667
- Wildcard (\*) is used to represent all columns.
If you input a specific column, the number of non-null values in the column is returned.

668

669
### ELAPSED
670

671
```sql
G
Ganlin Zhao 已提交
672
ELAPSED(ts_primary_key [, time_unit])
673 674
```

sangshuduo's avatar
sangshuduo 已提交
675
**Description**`elapsed` function can be used to calculate the continuous time length in which there is valid data. If it's used with `INTERVAL` clause, the returned result is the calculated time length within each time window. If it's used without `INTERVAL` caluse, the returned result is the calculated time length within the specified time range. Please be noted that the return value of `elapsed` is the number of `time_unit` in the calculated time length.
676

677
**Return value type**: Double if the input value is not NULL;
678

679
**Return value type**: TIMESTAMP
680

sangshuduo's avatar
sangshuduo 已提交
681
**Applicable tables**: table, STable, outer in nested query
682

683
**Explanations**
G
Ganlin Zhao 已提交
684
- `ts_primary_key` parameter can only be the first column of a table, i.e. timestamp primary key.
685 686
- The minimum value of `time_unit` is the time precision of the database. If `time_unit` is not specified, the time precision of the database is used as the default time unit. Time unit specified by `time_unit` can be:
          1b (nanoseconds), 1u (microseconds), 1a (milliseconds), 1s (seconds), 1m (minutes), 1h (hours), 1d (days), or 1w (weeks)
687 688 689 690
- It can be used with `INTERVAL` to get the time valid time length of each time window. Please be noted that the return value is same as the time window for all time windows except for the first and the last time window.
- `order by asc/desc` has no effect on the result.
- `group by tbname` must be used together when `elapsed` is used against a STable.
- `group by` must NOT be used together when `elapsed` is used against a table or sub table.
691 692 693
- When used in nested query, it's only applicable when the inner query outputs an implicit timestamp column as the primary key. For example, `select elapsed(ts) from (select diff(value) from sub1)` is legal usage while `select elapsed(ts) from (select * from sub1)` is not. In addition, because elapsed has a strict dependency on the timeline, a statement like `select elapsed(ts) from (select diff(value) from st group by tbname) will return a meaningless result.
- It can't be used with `leastsquares`, `diff`, `derivative`, `top`, `bottom`, `last_row`, `interp`.

694

695
### LEASTSQUARES
696

697
```sql
G
Ganlin Zhao 已提交
698
LEASTSQUARES(expr, start_val, step_val)
699
```
700

701
**Description**: The linear regression function of the specified column and the timestamp column (primary key), `start_val` is the initial value and `step_val` is the step value.
702

703 704 705
**Return value type**: A string in the format of "(slope, intercept)"

**Applicable data types**: Numeric
706

707
**Applicable table types**: table only
708

709

710
### SPREAD
711

712
```sql
G
Ganlin Zhao 已提交
713
SPREAD(expr)
714 715
```

716
**Description**: The difference between the max and the min of a specific column
717

718
**Return value type**: DOUBLE
719

720
**Applicable data types**: Integers and timestamps
721

722
**Applicable table types**: standard tables and supertables
723 724


725
### STDDEV
726

727
```sql
G
Ganlin Zhao 已提交
728
STDDEV(expr)
729 730
```

731
**Description**: Standard deviation of a specific column in a table or STable
732

733
**Return value type**: DOUBLE
734

735 736 737
**Applicable data types**: Numeric

**Applicable table types**: standard tables and supertables
738 739


740
### SUM
741

742
```sql
G
Ganlin Zhao 已提交
743
SUM(expr)
744
```
745

746 747 748
**Description**: The sum of a specific column in a table or STable

**Return value type**: DOUBLE or BIGINT
749

750
**Applicable data types**: Numeric
751

752
**Applicable table types**: standard tables and supertables
753 754


755
### HYPERLOGLOG
756

757
```sql
G
Ganlin Zhao 已提交
758
HYPERLOGLOG(expr)
759
```
760

761 762
**Description**:
  The cardinal number of a specific column is returned by using hyperloglog algorithm. The benefit of using hyperloglog algorithm is that the memory usage is under control when the data volume is huge.
sangshuduo's avatar
sangshuduo 已提交
763
  However, when the data volume is very small, the result may be not accurate, it's recommended to use `select count(data) from (select unique(col) as data from table)` in this case.
764 765

**Return value type**: Integer
766

767
**Applicable data types**: Numeric
768

769
**Applicable table types**: standard tables and supertables
770 771


772
### HISTOGRAM
773

774
```sql
G
Ganlin Zhao 已提交
775
HISTOGRAM(expr,bin_type, bin_description, normalized)
776
```
777

778
**Description**:Returns count of data points in user-specified ranges.
779

780
**Return value type** If normalized is set to 1, a DOUBLE is returned; otherwise a BIGINT is returned
781

782
**Applicable data types**: Numeric
783

784
**Applicable table types**: table, STable
785

786
**Explanations**:
G
Ganlin Zhao 已提交
787
- bin_type: parameter to indicate the bucket type, valid inputs are: "user_input", "linear_bin", "log_bin"。
G
Ganlin Zhao 已提交
788 789
- bin_description: parameter to describe how to generate buckets,can be in the following JSON formats for each bin_type respectively:
    - "user_input": "[1, 3, 5, 7]":
790
       User specified bin values.
G
Ganlin Zhao 已提交
791

792 793 794
    - "linear_bin": "{"start": 0.0, "width": 5.0, "count": 5, "infinity": true}"
       "start" - bin starting point.       "width" - bin offset.       "count" - number of bins generated.       "infinity" - whether to add(-inf, inf)as start/end point in generated set of bins.
       The above "linear_bin" descriptor generates a set of bins: [-inf, 0.0, 5.0, 10.0, 15.0, 20.0, +inf].
G
Ganlin Zhao 已提交
795

796 797 798 799
    - "log_bin": "{"start":1.0, "factor": 2.0, "count": 5, "infinity": true}"
       "start" - bin starting point.       "factor" - exponential factor of bin offset.       "count" - number of bins generated.       "infinity" - whether to add(-inf, inf)as start/end point in generated range of bins.
       The above "linear_bin" descriptor generates a set of bins: [-inf, 1.0, 2.0, 4.0, 8.0, 16.0, +inf].
- normalized: setting to 1/0 to turn on/off result normalization. Valid values are 0 or 1.
800 801


G
Ganlin Zhao 已提交
802
### PERCENTILE
803

804
```sql
G
Ganlin Zhao 已提交
805
PERCENTILE(expr, p [, p1] ...)
806
```
807

G
Ganlin Zhao 已提交
808
**Description**: The value whose rank in a specific column matches the specified percentage. If such a value matching the specified percentage doesn't exist in the column, an interpolation value will be returned.
809

sangshuduo's avatar
sangshuduo 已提交
810
**Return value type**: This function takes 2 minimum and 11 maximum parameters, and it can simultaneously return 10 percentiles at most. If 2 parameters are given, a single percentile is returned and the value type is DOUBLE.
G
Ganlin Zhao 已提交
811
                       If more than 2 parameters are given, the return value type is a VARCHAR string, the format of which is a JSON ARRAY containing all return values.
812

813
**Applicable column types**: Numeric
814

815
**Applicable table types**: table only
816

G
Ganlin Zhao 已提交
817
**More explanations**:
818

G
Ganlin Zhao 已提交
819
- _p_ is in range [0,100], when _p_ is 0, the result is same as using function MIN; when _p_ is 100, the result is same as function MAX.
sangshuduo's avatar
sangshuduo 已提交
820
- When calculating multiple percentiles of a specific column, a single PERCENTILE function with multiple parameters is advised, as this can largely reduce the query response time.
G
Ganlin Zhao 已提交
821
  For example, using SELECT percentile(col, 90, 95, 99) FROM table will perform better than SELECT percentile(col, 90), percentile(col, 95), percentile(col, 99) from table.
822

823 824 825
## Selection Functions

Selection functions return one or more results depending. You can specify the timestamp column, tbname pseudocolumn, or tag columns to show which rows contain the selected value.
826

827
### BOTTOM
828

829
```sql
G
Ganlin Zhao 已提交
830
BOTTOM(expr, k)
831 832
```

833
**Description**: The least _k_ values of a specific column in a table or STable. If a value has multiple occurrences in the column but counting all of them in will exceed the upper limit _k_, then a part of them will be returned randomly.
834

835
**Return value type**:Same as the data type of the column being operated upon
836

837
**Applicable data types**: Numeric
838

839
**Applicable table types**: standard tables and supertables
840

841
**More explanation**:
842

843 844 845
- _k_ must be in range [1,100]
- The timestamp associated with the selected values are returned too
- Can't be used with `FILL`
846

847
### FIRST
848

849
```sql
G
Ganlin Zhao 已提交
850
FIRST(expr)
851 852
```

853
**Description**: The first non-null value of a specific column in a table or STable
854

855
**Return value type**:Same as the data type of the column being operated upon
856

857
**Applicable data types**: Numeric
858

859
**Applicable table types**: standard tables and supertables
860

861
**More explanation**:
862

863 864 865
- FIRST(\*) can be used to get the first non-null value of all columns
- NULL will be returned if all the values of the specified column are all NULL
- A result will NOT be returned if all the columns in the result set are all NULL
866

867
### INTERP
868

869
```sql
G
Ganlin Zhao 已提交
870
INTERP(expr)
871 872
```

873
**Description**: The value that matches the specified timestamp range is returned, if existing; or an interpolation value is returned.
874

875
**Return value type**: Same as the column being operated upon
876

877
**Applicable data types**: Numeric
878

879
**Applicable table types**: standard tables and supertables
880

881
**More explanations**
882

883 884
- `INTERP` is used to get the value that matches the specified time slice from a column. If no such value exists an interpolation value will be returned based on `FILL` parameter.
- The input data of `INTERP` is the value of the specified column and a `where` clause can be used to filter the original data. If no `where` condition is specified then all original data is the input.
G
Ganlin Zhao 已提交
885
- `INTERP` must be used along with `RANGE`, `EVERY`, `FILL` keywords.
886
- The output time range of `INTERP` is specified by `RANGE(timestamp1,timestamp2)` parameter, with timestamp1 <= timestamp2. timestamp1 is the starting point of the output time range and must be specified. timestamp2 is the ending point of the output time range and must be specified.
G
Ganlin Zhao 已提交
887
- The number of rows in the result set of `INTERP` is determined by the parameter `EVERY(time_unit)`. Starting from timestamp1, one interpolation is performed for every time interval specified `time_unit` parameter. The parameter `time_unit` must be an integer, with no quotes, with a time unit of: a(millisecond)), s(second), m(minute), h(hour), d(day), or w(week). For example, `EVERY(500a)` will interpolate every 500 milliseconds.
888
- Interpolation is performed based on `FILL` parameter. For more information about FILL clause, see [FILL Clause](../distinguished/#fill-clause).
G
Ganlin Zhao 已提交
889
- `INTERP` can only be used to interpolate in single timeline. So it must be used with `partition by tbname` when it's used on a STable.
890 891
- Pseudocolumn `_irowts` can be used along with `INTERP` to return the timestamps associated with interpolation points(support after version 3.0.2.0).
- Pseudocolumn `_isfilled` can be used along with `INTERP` to indicate whether the results are original records or data points generated by interpolation algorithm(support after version 3.0.3.0).
892

893 894 895 896 897 898 899 900 901
**Example**

- We use the smart meters example used in this documentation to illustrate how to use the INTERP function.
- We want to downsample every 1 hour and use a linear fill for missing values. Note the order in which the "partition by" clause and the "range", "every" and "fill" parameters are used.

```sql
SELECT _irowts,INTERP(current) FROM test.meters PARTITION BY TBNAME RANGE('2017-07-22 00:00:00','2017-07-24 12:25:00') EVERY(1h) FILL(LINEAR) 
```

902
### LAST
903

904
```sql
G
Ganlin Zhao 已提交
905
LAST(expr)
906 907
```

908
**Description**: The last non-NULL value of a specific column in a table or STable
909

910
**Return value type**:Same as the data type of the column being operated upon
911

912
**Applicable data types**: Numeric
913

914
**Applicable table types**: standard tables and supertables
915

916
**More explanation**:
917

918 919 920
- LAST(\*) can be used to get the last non-NULL value of all columns
- If the values of a column in the result set are all NULL, NULL is returned for that column; if all columns in the result are all NULL, no result will be returned.
- When it's used on a STable, if there are multiple values with the timestamp in the result set, one of them will be returned randomly and it's not guaranteed that the same value is returned if the same query is run multiple times.
921

922

923
### LAST_ROW
924

925
```sql
G
Ganlin Zhao 已提交
926
LAST_ROW(expr)
927 928
```

929
**Description**: The last row of a table or STable
930

931
**Return value type**:Same as the data type of the column being operated upon
932

933
**Applicable data types**: Numeric
934

935
**Applicable table types**: standard tables and supertables
936 937 938

**More explanations**:

939 940
- When it's used on a STable, if there are multiple values with the timestamp in the result set, one of them will be returned randomly and it's not guaranteed that the same value is returned if the same query is run multiple times.
- Can't be used with `INTERVAL`.
941

942
### MAX
943

944
```sql
G
Ganlin Zhao 已提交
945
MAX(expr)
946 947
```

948 949 950
**Description**: The maximum value of a specific column of a table or STable

**Return value type**:Same as the data type of the column being operated upon
951

G
Ganlin Zhao 已提交
952
**Applicable data types**: Numeric
953

954
**Applicable table types**: standard tables and supertables
955 956


957
### MIN
958

959
```sql
G
Ganlin Zhao 已提交
960
MIN(expr)
961
```
962

963 964 965
**Description**: The minimum value of a specific column in a table or STable

**Return value type**:Same as the data type of the column being operated upon
966

G
Ganlin Zhao 已提交
967
**Applicable data types**: Numeric
968

969
**Applicable table types**: standard tables and supertables
970 971


G
Ganlin Zhao 已提交
972
### MODE
973

974
```sql
G
Ganlin Zhao 已提交
975
MODE(expr)
976
```
977

G
Ganlin Zhao 已提交
978
**Description**:The value which has the highest frequency of occurrence. One random value is returned if there are multiple values which have highest frequency of occurrence.
979

980
**Return value type**: Same as the input data
981

982 983 984
**Applicable data types**: Numeric

**Applicable table types**: standard tables and supertables
985 986


G
Ganlin Zhao 已提交
987 988 989
### SAMPLE

```sql
G
Ganlin Zhao 已提交
990
SAMPLE(expr, k)
G
Ganlin Zhao 已提交
991 992 993 994
```

**Description**: _k_ sampling values of a specific column. The applicable range of _k_ is [1,1000].

995
**Return value type**: Same as the column being operated plus the associated timestamp
G
Ganlin Zhao 已提交
996

997
**Applicable data types**: Any data type except for tags of STable
G
Ganlin Zhao 已提交
998

999
**Applicable nested query**: Inner query and Outer query
G
Ganlin Zhao 已提交
1000

1001
**Applicable table types**: standard tables and supertables
G
Ganlin Zhao 已提交
1002

G
Ganlin Zhao 已提交
1003
**More explanations**:
1004 1005 1006

This function cannot be used in expression calculation.
- Must be used with `PARTITION BY tbname` when it's used on a STable to force the result on each single timeline
G
Ganlin Zhao 已提交
1007

1008

1009
### TAIL
1010

1011
```sql
G
Ganlin Zhao 已提交
1012
TAIL(expr, k, offset_val)
1013 1014
```

1015
**Description**: The next _k_ rows are returned after skipping the last `offset_val` rows, NULL values are not ignored. `offset_val` is optional parameter. When it's not specified, the last _k_ rows are returned. When `offset_val` is used, the effect is same as `order by ts desc LIMIT k OFFSET offset_val`.
1016

1017 1018 1019
**Parameter value range**: k: [1,100] offset_val: [0,100]

**Return value type**:Same as the data type of the column being operated upon
1020

1021 1022 1023
**Applicable data types**: Any data type except for timestamp, i.e. the primary key

**Applicable table types**: standard tables and supertables
1024 1025


1026
### TOP
1027

1028
```sql
G
Ganlin Zhao 已提交
1029
TOP(expr, k)
1030 1031
```

1032
**Description**: The greatest _k_ values of a specific column in a table or STable. If a value has multiple occurrences in the column but counting all of them in will exceed the upper limit _k_, then a part of them will be returned randomly.
1033

1034
**Return value type**:Same as the data type of the column being operated upon
1035

1036
**Applicable data types**: Numeric
1037

1038
**Applicable table types**: standard tables and supertables
1039

1040
**More explanation**:
1041

1042 1043 1044
- _k_ must be in range [1,100]
- The timestamp associated with the selected values are returned too
- Can't be used with `FILL`
1045

1046
### UNIQUE
1047

1048
```sql
G
Ganlin Zhao 已提交
1049
UNIQUE(expr)
1050 1051
```

1052
**Description**: The values that occur the first time in the specified column. The effect is similar to `distinct` keyword, but it can also be used to match tags or timestamp. The first occurrence of a timestamp or tag is used.
1053

1054
**Return value type**:Same as the data type of the column being operated upon
1055

1056
**Applicable column types**: Any data types except for timestamp
1057

1058
**Applicable table types**: table, STable
1059 1060


1061
## Time-Series Extensions
1062

1063
TDengine includes extensions to standard SQL that are intended specifically for time-series use cases. The functions enabled by these extensions require complex queries to implement in general-purpose databases. By offering them as built-in extensions, TDengine reduces user workload.
1064

1065
### CSUM
1066

1067
```sql
G
Ganlin Zhao 已提交
1068
CSUM(expr)
1069 1070
```

1071
**Description**: The cumulative sum of each row for a specific column. The number of output rows is same as that of the input rows.
1072

1073
**Return value type**: Long integer for integers; Double for floating points. uint64_t for unsigned integers
1074

1075
**Applicable data types**: Numeric
1076

1077
**Nested query**: It can be used in both the outer query and inner query in a nested query.
1078

1079 1080
**Applicable table types**: standard tables and supertables

G
Ganlin Zhao 已提交
1081 1082
**More explanations**:

1083
- Arithmetic operation can't be performed on the result of `csum` function
G
Ganlin Zhao 已提交
1084
- Can only be used with aggregate functions This function can be used with supertables and standard tables.
1085
- Must be used with `PARTITION BY tbname` when it's used on a STable to force the result on each single timeline
1086 1087


1088
### DERIVATIVE
1089

1090
```sql
G
Ganlin Zhao 已提交
1091 1092 1093 1094 1095 1096
DERIVATIVE(expr, time_inerval, ignore_negative)

ignore_negative: {
    0
  | 1
}
1097 1098
```

1099
**Description**: The derivative of a specific column. The time rage can be specified by parameter `time_interval`, the minimum allowed time range is 1 second (1s); the value of `ignore_negative` can be 0 or 1, 1 means negative values are ignored.
1100

1101
**Return value type**: DOUBLE
1102

1103
**Applicable data types**: Numeric
1104

1105
**Applicable table types**: standard tables and supertables
1106

G
Ganlin Zhao 已提交
1107 1108
**More explanation**:

1109
- It can be used together with `PARTITION BY tbname` against a STable.
1110
- It can be used together with a selected column. For example: select \_rowts, DERIVATIVE() from。
1111

1112
### DIFF
1113

1114
```sql
G
Ganlin Zhao 已提交
1115 1116 1117 1118 1119 1120
DIFF(expr [, ignore_negative])

ignore_negative: {
    0
  | 1
}
1121 1122
```

1123
**Description**: The different of each row with its previous row for a specific column. `ignore_negative` can be specified as 0 or 1, the default value is 1 if it's not specified. `1` means negative values are ignored.
1124

1125
**Return value type**:Same as the data type of the column being operated upon
1126

1127
**Applicable data types**: Numeric
1128

1129
**Applicable table types**: standard tables and supertables
1130

G
Ganlin Zhao 已提交
1131
**More explanation**:
1132 1133 1134

- The number of result rows is the number of rows subtracted by one, no output for the first row
- It can be used together with a selected column. For example: select \_rowts, DIFF() from。
1135 1136


1137
### IRATE
1138

1139
```sql
G
Ganlin Zhao 已提交
1140
IRATE(expr)
1141 1142
```

1143
**Description**: instantaneous rate on a specific column. The last two samples in the specified time range are used to calculate instantaneous rate. If the last sample value is smaller, then only the last sample value is used instead of the difference between the last two sample values.
1144

1145
**Return value type**: DOUBLE
1146

1147
**Applicable data types**: Numeric
1148

1149
**Applicable table types**: standard tables and supertables
1150 1151


1152
### MAVG
1153

1154
```sql
G
Ganlin Zhao 已提交
1155
MAVG(expr, k)
1156 1157
```

1158
**Description**: The moving average of continuous _k_ values of a specific column. If the number of input rows is less than _k_, nothing is returned. The applicable range of _k_ is [1,1000].
1159

1160
**Return value type**: DOUBLE
1161

1162
**Applicable data types**: Numeric
1163

1164
**Nested query**: It can be used in both the outer query and inner query in a nested query.
1165

1166
**Applicable table types**: standard tables and supertables
1167

G
Ganlin Zhao 已提交
1168 1169 1170
**More explanations**:

- Arithmetic operation can't be performed on the result of `MAVG`.
1171 1172
- Can only be used with data columns, can't be used with tags. - Can't be used with aggregate functions.
- Must be used with `PARTITION BY tbname` when it's used on a STable to force the result on each single timeline
1173 1174 1175 1176


### STATECOUNT

1177
```sql
G
Ganlin Zhao 已提交
1178
STATECOUNT(expr, oper, val)
1179 1180
```

1181
**Description**: The number of continuous rows satisfying the specified conditions for a specific column. The result is shown as an extra column for each row. If the specified condition is evaluated as true, the number is increased by 1; otherwise the number is reset to -1. If the input value is NULL, then the corresponding row is skipped.
1182 1183 1184

**Applicable parameter values**:

S
Sean Ely 已提交
1185
- oper : Can be one of `'LT'` (lower than), `'GT'` (greater than), `'LE'` (lower than or equal to), `'GE'` (greater than or equal to), `'NE'` (not equal to), `'EQ'` (equal to), the value is case insensitive, the value must be in quotes.
1186
- val : Numeric types
1187

1188
**Return value type**: Integer
1189

1190
**Applicable data types**: Numeric
1191

1192
**Applicable nested query**: Outer query only
1193

1194
**Applicable table types**: standard tables and supertables
1195 1196 1197

**More explanations**:

1198 1199 1200
- Must be used together with `PARTITION BY tbname` when it's used on a STable to force the result into each single timeline]
- Can't be used with window operation, like interval/state_window/session_window

1201 1202 1203

### STATEDURATION

1204
```sql
G
Ganlin Zhao 已提交
1205
STATEDURATION(expr, oper, val, unit)
1206 1207
```

1208
**Description**: The length of time range in which all rows satisfy the specified condition for a specific column. The result is shown as an extra column for each row. The length for the first row that satisfies the condition is 0. Next, if the condition is evaluated as true for a row, the time interval between current row and its previous row is added up to the time range; otherwise the time range length is reset to -1. If the value of the column is NULL, the corresponding row is skipped.
1209 1210 1211

**Applicable parameter values**:

S
Sean Ely 已提交
1212
- oper : Can be one of `'LT'` (lower than), `'GT'` (greater than), `'LE'` (lower than or equal to), `'GE'` (greater than or equal to), `'NE'` (not equal to), `'EQ'` (equal to), the value is case insensitive, the value must be in quotes.
1213 1214
- val : Numeric types
- unit: The unit of time interval. Enter one of the following options: 1b (nanoseconds), 1u (microseconds), 1a (milliseconds), 1s (seconds), 1m (minutes), 1h (hours), 1d (days), or 1w (weeks) If you do not enter a unit of time, the precision of the current database is used by default.
1215

1216
**Return value type**: Integer
1217

1218
**Applicable data types**: Numeric
1219

1220
**Applicable nested query**: Outer query only
1221

1222
**Applicable table types**: standard tables and supertables
1223 1224 1225

**More explanations**:

1226 1227 1228
- Must be used together with `PARTITION BY tbname` when it's used on a STable to force the result into each single timeline]
- Can't be used with window operation, like interval/state_window/session_window

1229

1230
### TWA
1231

1232
```sql
G
Ganlin Zhao 已提交
1233
TWA(expr)
1234 1235
```

1236
**Description**: Time weighted average on a specific column within a time range
1237

1238
**Return value type**: DOUBLE
1239

1240
**Applicable data types**: Numeric
1241

1242
**Applicable table types**: standard tables and supertables
1243

1244
- Must be used together with `PARTITION BY tbname` to force the result into each single timeline.
1245 1246


1247
## System Information Functions
1248

1249
### DATABASE
1250

1251
```sql
1252
SELECT DATABASE();
1253 1254
```

1255 1256
**Description**: The current database. If no database is specified upon logging in and no database is specified with `USE` after login, NULL will be returned by `select database()`.

1257

1258
### CLIENT_VERSION
1259

1260
```sql
1261
SELECT CLIENT_VERSION();
1262 1263
```

1264
**Description**: The client version.
1265

1266
### SERVER_VERSION
1267

1268
```sql
1269
SELECT SERVER_VERSION();
1270 1271
```

1272
**Description**: The server version.
1273

1274
### SERVER_STATUS
1275

1276
```sql
S
Sean Ely 已提交
1277
SELECT SERVER_STATUS();
1278 1279
```

1280
**Description**: The server status.