OpenSearch:UDAF usage

UDAF list

• sum: Calculates the sum of the values after aggregation.

• avg: Calculates the average of the values after aggregation.

• max: Calculates the maximum value after aggregation.

• min: Calculates the minimum value after aggregation.

• count: Counts the number of items after aggregation.

• MAXLABEL: Retrieves the label value that corresponds to the maximum value after aggregation.

Usage examples

Test data

The following examples use the phone table in the staging environment, which contains mobile phone information from major brands.

nid	title	price	brand	size	color
1	Huawei Mate 9 Kirin 960 chip Leica dual lens	3599	Huawei	5.9	Red
2	Huawei/Huawei P10 Plus full Netcom phone	4388	Huawei	5.5	Blue
3	Xiaomi/Xiaomi Redmi 4X 32G full Netcom 4G smartphone	899	Xiaomi	5.0	Black
4	OPPO R11 full Netcom front and rear 20-megapixel fingerprint recognition camera phone r11r9s	2999	OPPO	5.5	Red
5	Meizu/Meizu Meilan E2 full Netcom front fingerprint fast-charge 4G smartphone	1299	Meizu	5.5	Silver white
6	Nokia/Nokia 105 Mobile loud senior phone candy bar keypad student elderly small phone long standby	169	Nokia	1.4	Blue
7	Apple/Apple iPhone 6s 32G original sealed domestic version in stock fast shipping	3599	Apple	4.7	Silver white
8	Apple/Apple iPhone 7 Plus 128G full Netcom 4G phone	5998	Apple	5.5	Jet black
9	Apple/Apple iPhone 7 32G full Netcom 4G smartphone	4298	Apple	4.7	Black
10	Samsung/Samsung GALAXY S8 SM-G9500 full Netcom 4G phone	5688	Samsung	5.6	Misty blue

Query examples

● Retrieve all content from the table.

SELECT * FROM phone ORDER BY nid LIMIT 1000 

USE_TIME: 0.036, ROW_COUNT: 10

------------------------------- TABLE INFO ---------------------------
                 nid |               title |               price |               brand |                size |               color |
                   1 |                null |                3599 |              Huawei |                 5.9 |                null |
                   2 |                null |                4388 |              Huawei |                 5.5 |                null |
                   3 |                null |                 899 |              Xiaomi |                   5 |                null |
                   4 |                null |                2999 |                OPPO |                 5.5 |                null |
                   5 |                null |                1299 |               Meizu |                 5.5 |                null |
                   6 |                null |                 169 |               Nokia |                 1.4 |                null |
                   7 |                null |                3599 |               Apple |                 4.7 |                null |
                   8 |                null |                5998 |               Apple |                 5.5 |                null |
                   9 |                null |                4298 |               Apple |                 4.7 |                null |
                  10 |                null |                5688 |             Samsung |                 5.6 |                null |

Note: The title and color fields are summary fields and are displayed as null.

• Calculate the total price of products for each brand by using the sum function.

SELECT brand, sum(price) FROM phone GROUP BY (brand) ORDER BY brand LIMIT 1000

USE_TIME: 0.152, ROW_COUNT: 7

------------------------------- TABLE INFO ---------------------------
               brand |          SUM(price) |
               Apple |               13895 |
              Huawei |                7987 |
               Meizu |                1299 |
               Nokia |                 169 |
                OPPO |                2999 |
             Samsung |                5688 |
              Xiaomi |                 899 |

• Find the highest-priced phone for each brand by using the max function, sorted by price in descending order.

SELECT brand, max(price) AS price FROM phone GROUP BY (brand) ORDER BY price DESC LIMIT 1000

USE_TIME: 0.053, ROW_COUNT: 7

------------------------------- TABLE INFO ---------------------------
               brand |               price |
               Apple |                5998 |
             Samsung |                5688 |
              Huawei |                4388 |
                OPPO |                2999 |
               Meizu |                1299 |
              Xiaomi |                 899 |
               Nokia |                 169 |

• Find the screen size of the highest-priced phone for each brand by using the MAXLABEL function.

SELECT brand, MAXLABEL(size, price) AS size FROM phone GROUP BY brand