| <!DOCTYPE html><html lang="en"><head><meta charSet="utf-8"/><meta http-equiv="X-UA-Compatible" content="IE=edge"/><title>Expressions · Apache Druid</title><meta name="viewport" content="width=device-width"/><link rel="canonical" href="https://druid.apache.org/docs/latest/misc/math-expr.html"/><meta name="generator" content="Docusaurus"/><meta name="description" content="<!--"/><meta name="docsearch:language" content="en"/><meta name="docsearch:version" content="0.23.0" /><meta property="og:title" content="Expressions · Apache Druid"/><meta property="og:type" content="website"/><meta property="og:url" content="https://druid.apache.org/index.html"/><meta property="og:description" content="<!--"/><meta property="og:image" content="https://druid.apache.org/img/druid_nav.png"/><meta name="twitter:card" content="summary"/><meta name="twitter:image" content="https://druid.apache.org/img/druid_nav.png"/><link rel="shortcut icon" href="/img/favicon.png"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/docsearch.js/1/docsearch.min.css"/><link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/default.min.css"/><script async="" src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script><script> |
| window.dataLayer = window.dataLayer || []; |
| function gtag(){dataLayer.push(arguments); } |
| gtag('js', new Date()); |
| gtag('config', 'UA-131010415-1'); |
| </script><link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css"/><link rel="stylesheet" href="/css/code-block-buttons.css"/><script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/clipboard.js/2.0.4/clipboard.min.js"></script><script type="text/javascript" src="/js/code-block-buttons.js"></script><script src="/js/scrollSpy.js"></script><link rel="stylesheet" href="/css/main.css"/><script src="/js/codetabs.js"></script></head><body class="sideNavVisible separateOnPageNav"><div class="fixedHeaderContainer"><div class="headerWrapper wrapper"><header><a href="/"><img class="logo" src="/img/druid_nav.png" alt="Apache Druid"/></a><div class="navigationWrapper navigationSlider"><nav class="slidingNav"><ul class="nav-site nav-site-internal"><li class=""><a href="/technology" target="_self">Technology</a></li><li class=""><a href="/use-cases" target="_self">Use Cases</a></li><li class=""><a href="/druid-powered" target="_self">Powered By</a></li><li class="siteNavGroupActive"><a href="/docs/latest/design/index.html" target="_self">Docs</a></li><li class=""><a href="/community/" target="_self">Community</a></li><li class=""><a href="https://www.apache.org" target="_self">Apache</a></li><li class=""><a href="/downloads.html" target="_self">Download</a></li><li class="navSearchWrapper reactNavSearchWrapper"><input type="text" id="search_input_react" placeholder="Search" title="Search"/></li></ul></nav></div></header></div></div><div class="navPusher"><div class="docMainWrapper wrapper"><div class="docsNavContainer" id="docsNav"><nav class="toc"><div class="toggleNav"><section class="navWrapper wrapper"><div class="navBreadcrumb wrapper"><div class="navToggle" id="navToggler"><div class="hamburger-menu"><div class="line1"></div><div class="line2"></div><div class="line3"></div></div></div><h2><i>›</i><span>Native query components</span></h2><div class="tocToggler" id="tocToggler"><i class="icon-toc"></i></div></div><div class="navGroups"><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Getting started<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/design/index.html">Introduction to Apache Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/index.html">Quickstart</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/docker.html">Docker</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/single-server.html">Single server deployment</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/cluster.html">Clustered deployment</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Tutorials<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-batch.html">Loading files natively</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-kafka.html">Load from Apache Kafka</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-batch-hadoop.html">Load from Apache Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-query.html">Querying data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-rollup.html">Roll-up</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-retention.html">Configuring data retention</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-update-data.html">Updating existing data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-compaction.html">Compacting segments</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-delete-data.html">Deleting data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-ingestion-spec.html">Writing an ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-transform-spec.html">Transforming input data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-kerberos-hadoop.html">Kerberized HDFS deep storage</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Design<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/design/architecture.html">Design</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/segments.html">Segments</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/processes.html">Processes and servers</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/dependencies/deep-storage.html">Deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/dependencies/metadata-storage.html">Metadata storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/dependencies/zookeeper.html">ZooKeeper</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Ingestion<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/index.html">Ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/data-formats.html">Data formats</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/data-model.html">Data model</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/rollup.html">Data rollup</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/partitioning.html">Partitioning</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/ingestion-spec.html">Ingestion spec</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/schema-design.html">Schema design tips</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/data-management.html">Data management</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/compaction.html">Compaction</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Stream ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-ingestion.html">Apache Kafka ingestion</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-supervisor-reference.html">Apache Kafka supervisor</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-supervisor-operations.html">Apache Kafka operations</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kinesis-ingestion.html">Amazon Kinesis</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Batch ingestion</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch.html">Native batch</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-simple-task.html">Simple task indexing</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-input-sources.html">Input sources</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-firehose.html">Firehose</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/hadoop.html">Hadoop-based</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/tasks.html">Task reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/faq.html">Troubleshooting FAQ</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Querying<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Druid SQL</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql.html">Overview and syntax</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-data-types.html">SQL data types</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-operators.html">Operators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-scalar.html">Scalar functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-aggregations.html">Aggregation functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-multivalue-string-functions.html">Multi-value string functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-api.html">Druid SQL API</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-jdbc.html">JDBC driver API</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-query-context.html">SQL query context</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-metadata-tables.html">SQL metadata tables</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-translation.html">SQL query translation</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/latest/querying/querying.html">Native queries</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/query-execution.html">Query execution</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/troubleshooting.html">Troubleshooting</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Concepts</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/querying/datasource.html">Datasources</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/joins.html">Joins</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/lookups.html">Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/multi-value-dimensions.html">Multi-value dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/multitenancy.html">Multitenancy</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/caching.html">Query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/using-caching.html">Using query caching</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/query-context.html">Query context</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query types</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/querying/timeseriesquery.html">Timeseries</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/topnquery.html">TopN</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/groupbyquery.html">GroupBy</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/scan-query.html">Scan</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/searchquery.html">Search</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/timeboundaryquery.html">TimeBoundary</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/segmentmetadataquery.html">SegmentMetadata</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/datasourcemetadataquery.html">DatasourceMetadata</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Native query components</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/querying/filters.html">Filters</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/granularities.html">Granularities</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/dimensionspecs.html">Dimensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/aggregations.html">Aggregations</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/post-aggregations.html">Post-aggregations</a></li><li class="navListItem navListItemActive"><a class="navItem" href="/docs/latest/misc/math-expr.html">Expressions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/having.html">Having filters (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/limitspec.html">Sorting and limiting (groupBy)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/topnmetricspec.html">Sorting (topN)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sorting-orders.html">String comparators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/virtual-columns.html">Virtual columns</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/geo.html">Spatial filters</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Configuration<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/configuration/index.html">Configuration reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions.html">Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/configuration/logging.html">Logging</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Operations<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/operations/druid-console.html">Web console</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/getting-started.html">Getting started with Apache Druid</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Security</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/operations/security-overview.html">Security overview</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/security-user-auth.html">User authentication and authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/auth-ldap.html">LDAP auth</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/password-provider.html">Password providers</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/dynamic-config-provider.html">Dynamic Config Providers</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/tls-support.html">TLS support</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Performance tuning</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/operations/basic-cluster-tuning.html">Basic cluster tuning</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/segment-optimization.html">Segment Size Optimization</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/mixed-workloads.html">Mixed workloads</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/http-compression.html">HTTP compression</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/clean-metadata-store.html">Automated metadata cleanup</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Monitoring</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/operations/request-logging.html">Request logging</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/metrics.html">Metrics</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/alerts.html">Alerts</a></li></ul></div><li class="navListItem"><a class="navItem" href="/docs/latest/operations/api-reference.html">API reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/high-availability.html">High availability</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/rolling-updates.html">Rolling updates</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/rule-configuration.html">Retaining or automatically dropping data</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/other-hadoop.html">Working with different versions of Apache Hadoop</a></li><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">Misc</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/operations/management-uis.html">Legacy Management UIs</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/dump-segment.html">dump-segment tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/reset-cluster.html">reset-cluster tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/insert-segment-to-db.html">insert-segment-to-db tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/pull-deps.html">pull-deps tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/deep-storage-migration.html">Deep storage migration</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/export-metadata.html">Export Metadata Tool</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/metadata-migration.html">Metadata Migration</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/use_sbt_to_build_fat_jar.html">Content for build.sbt</a></li></ul></div></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Development<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/development/overview.html">Developing on Druid</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/modules.html">Creating extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/javascript.html">JavaScript functionality</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/build.html">Build from source</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/versioning.html">Versioning</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/experimental.html">Experimental features</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Misc<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/misc/papers-and-talks.html">Papers</a></li></ul></div><div class="navGroup"><h3 class="navGroupCategoryTitle collapsible">Hidden<span class="arrow"><svg width="24" height="24" viewBox="0 0 24 24"><path fill="#565656" d="M7.41 15.41L12 10.83l4.59 4.58L18 14l-6-6-6 6z"></path><path d="M0 0h24v24H0z" fill="none"></path></svg></span></h3><ul class="hide"><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-elasticsearch.html">Apache Druid vs Elasticsearch</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-key-value.html">Apache Druid vs. Key/Value Stores (HBase/Cassandra/OpenTSDB)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-kudu.html">Apache Druid vs Kudu</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-redshift.html">Apache Druid vs Redshift</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-spark.html">Apache Druid vs Spark</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/comparisons/druid-vs-sql-on-hadoop.html">Apache Druid vs SQL-on-Hadoop</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/auth.html">Authentication and Authorization</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/broker.html">Broker</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/coordinator.html">Coordinator Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/historical.html">Historical Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/indexer.html">Indexer Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/indexing-service.html">Indexing Service</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/middlemanager.html">MiddleManager Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/overlord.html">Overlord Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/router.html">Router Process</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/design/peons.html">Peons</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/approximate-histograms.html">Approximate Histogram aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/avro.html">Apache Avro</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/azure.html">Microsoft Azure</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/bloom-filter.html">Bloom Filter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-extension.html">DataSketches extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-hll.html">DataSketches HLL Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-quantiles.html">DataSketches Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-theta.html">DataSketches Theta Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/datasketches-tuple.html">DataSketches Tuple Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-basic-security.html">Basic Security</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-kerberos.html">Kerberos</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-lookups.html">Cached Lookup Module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-ranger-security.html">Apache Ranger Security</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/google.html">Google Cloud Storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/hdfs.html">HDFS</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kafka-extraction-namespace.html">Apache Kafka Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/lookups-cached-global.html">Globally Cached Lookups</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/mysql.html">MySQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/orc.html">ORC Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-pac4j.html">Druid pac4j based Security extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/parquet.html">Apache Parquet Extension</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/postgresql.html">PostgreSQL Metadata Store</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/protobuf.html">Protobuf</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/s3.html">S3-compatible</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/simple-client-sslcontext.html">Simple SSLContext Provider Module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/stats.html">Stats aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/test-stats.html">Test Stats Aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/druid-aws-rds.html">Druid AWS RDS Module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-core/kubernetes.html">Kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/ambari-metrics-emitter.html">Ambari Metrics Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/cassandra.html">Apache Cassandra</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/cloudfiles.html">Rackspace Cloud Files</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/distinctcount.html">DistinctCount Aggregator</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/graphite.html">Graphite Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/influx.html">InfluxDB Line Protocol Parser</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/influxdb-emitter.html">InfluxDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/kafka-emitter.html">Kafka Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/materialized-view.html">Materialized View</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/momentsketch-quantiles.html">Moment Sketches for Approximate Quantiles module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/moving-average-query.html">Moving Average Query</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/opentsdb-emitter.html">OpenTSDB Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/redis-cache.html">Druid Redis Cache</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/sqlserver.html">Microsoft SQLServer</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/statsd.html">StatsD Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/tdigestsketch-quantiles.html">T-Digest Quantiles Sketch module</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/thrift.html">Thrift</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/time-min-max.html">Timestamp Min/Max aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/gce-extensions.html">GCE Extensions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/aliyun-oss.html">Aliyun OSS</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/development/extensions-contrib/prometheus.html">Prometheus Emitter</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/kubernetes.html">kubernetes</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/hll-old.html">Cardinality/HyperUnique aggregators</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/select-query.html">Select</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/standalone-realtime.html">Realtime Process</a></li></ul></div></div></section></div><script> |
| var coll = document.getElementsByClassName('collapsible'); |
| var checkActiveCategory = true; |
| for (var i = 0; i < coll.length; i++) { |
| var links = coll[i].nextElementSibling.getElementsByTagName('*'); |
| if (checkActiveCategory){ |
| for (var j = 0; j < links.length; j++) { |
| if (links[j].classList.contains('navListItemActive')){ |
| coll[i].nextElementSibling.classList.toggle('hide'); |
| coll[i].childNodes[1].classList.toggle('rotate'); |
| checkActiveCategory = false; |
| break; |
| } |
| } |
| } |
| |
| coll[i].addEventListener('click', function() { |
| var arrow = this.childNodes[1]; |
| arrow.classList.toggle('rotate'); |
| var content = this.nextElementSibling; |
| content.classList.toggle('hide'); |
| }); |
| } |
| |
| document.addEventListener('DOMContentLoaded', function() { |
| createToggler('#navToggler', '#docsNav', 'docsSliderActive'); |
| createToggler('#tocToggler', 'body', 'tocActive'); |
| |
| var headings = document.querySelector('.toc-headings'); |
| headings && headings.addEventListener('click', function(event) { |
| var el = event.target; |
| while(el !== headings){ |
| if (el.tagName === 'A') { |
| document.body.classList.remove('tocActive'); |
| break; |
| } else{ |
| el = el.parentNode; |
| } |
| } |
| }, false); |
| |
| function createToggler(togglerSelector, targetSelector, className) { |
| var toggler = document.querySelector(togglerSelector); |
| var target = document.querySelector(targetSelector); |
| |
| if (!toggler) { |
| return; |
| } |
| |
| toggler.onclick = function(event) { |
| event.preventDefault(); |
| |
| target.classList.toggle(className); |
| }; |
| } |
| }); |
| </script></nav></div><div class="container mainContainer docsContainer"><div class="wrapper"><div class="post"><header class="postHeader"><a class="edit-page-link button" href="https://github.com/apache/druid/edit/master/docs/misc/math-expr.md" target="_blank" rel="noreferrer noopener">Edit</a><h1 id="__docusaurus" class="postHeaderTitle">Expressions</h1></header><article><div><span><!-- |
| ~ Licensed to the Apache Software Foundation (ASF) under one |
| ~ or more contributor license agreements. See the NOTICE file |
| ~ distributed with this work for additional information |
| ~ regarding copyright ownership. The ASF licenses this file |
| ~ to you under the Apache License, Version 2.0 (the |
| ~ "License"); you may not use this file except in compliance |
| ~ with the License. You may obtain a copy of the License at |
| ~ |
| ~ http://www.apache.org/licenses/LICENSE-2.0 |
| ~ |
| ~ Unless required by applicable law or agreed to in writing, |
| ~ software distributed under the License is distributed on an |
| ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| ~ KIND, either express or implied. See the License for the |
| ~ specific language governing permissions and limitations |
| ~ under the License. |
| --> |
| <blockquote> |
| <p>Apache Druid supports two query languages: <a href="/docs/latest/querying/querying.html">native queries</a> and <a href="/docs/latest/querying/sql.html">Druid SQL</a>. |
| This document describes the native language. For information about functions available in SQL, refer to the |
| <a href="/docs/latest/querying/sql-scalar.html">SQL documentation</a>.</p> |
| </blockquote> |
| <p>Expressions are used in various places in the native query language, including |
| <a href="/docs/latest/querying/virtual-columns.html">virtual columns</a> and <a href="/docs/latest/querying/datasource.html#join">join conditions</a>. They are |
| also generated by most <a href="/docs/latest/querying/sql-scalar.html">Druid SQL functions</a> during the |
| <a href="/docs/latest/querying/sql-translation.html">query translation</a> process.</p> |
| <p>Expressions are also used at ingestion time inside <a href="/docs/latest/ingestion/ingestion-spec.html#transforms">transformations</a>.</p> |
| <p>This expression language supports the following operators (listed in decreasing order of precedence).</p> |
| <table> |
| <thead> |
| <tr><th>Operators</th><th>Description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>!, -</td><td>Unary NOT and Minus</td></tr> |
| <tr><td>^</td><td>Binary power op</td></tr> |
| <tr><td>*, /, %</td><td>Binary multiplicative</td></tr> |
| <tr><td>+, -</td><td>Binary additive</td></tr> |
| <tr><td><, <=, >, >=, ==, !=</td><td>Binary Comparison</td></tr> |
| <tr><td>&&, ||</td><td>Binary Logical AND, OR</td></tr> |
| </tbody> |
| </table> |
| <p>Long, double, and string data types are supported. If a number contains a dot, it is interpreted as a double, otherwise it is interpreted as a long. That means, always add a '.' to your number if you want it interpreted as a double value. String literals should be quoted by single quotation marks.</p> |
| <p>Additionally, the expression language supports long, double, and string arrays. Array literals are created by wrapping square brackets around a list of scalar literals values delimited by a comma or space character. All values in an array literal must be the same type, however null values are accepted. Typed empty arrays may be defined by prefixing with their type in angle brackets: <code><STRING>[]</code>, <code><DOUBLE>[]</code>, or <code><LONG>[]</code>.</p> |
| <p>Expressions can contain variables. Variable names may contain letters, digits, '_' and '$'. Variable names must not begin with a digit. To escape other special characters, you can quote it with double quotation marks.</p> |
| <p>For logical operators, a number is true if and only if it is positive (0 or negative value means false). For string type, it's the evaluation result of 'Boolean.valueOf(string)'.</p> |
| <p><a href="/docs/latest/querying/multi-value-dimensions.html">Multi-value string dimensions</a> are supported and may be treated as either scalar or array typed values, as follows:</p> |
| <ul> |
| <li>When treated as a scalar type, the expression is automatically transformed so that the scalar operation is applied across all values of the multi-valued type, mimicking Druid's native behavior.</li> |
| <li>Druid coerces values that result in arrays back into the native Druid string type for grouping and aggregation. Grouping on multi-value string dimensions in Druid groups by the individual values, not the 'array'. This behavior produces results similar to the <code>UNNEST</code> operator available in many SQL dialects. Alternatively, you can use the <code>array_to_string</code> function to perform the aggregation on a <em>stringified</em> version of the complete array and therefore preserve the complete row. To transform the stringified dimension back into the true native array type, use <code>string_to_array</code> in an expression post-aggregator.</li> |
| </ul> |
| <p>The following built-in functions are available.</p> |
| <h2><a class="anchor" aria-hidden="true" id="general-functions"></a><a href="#general-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>General functions</h2> |
| <table> |
| <thead> |
| <tr><th>name</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>cast</td><td>cast(expr,'LONG' or 'DOUBLE' or 'STRING' or 'LONG_ARRAY', or 'DOUBLE_ARRAY' or 'STRING_ARRAY') returns expr with specified type. exception can be thrown. Scalar types may be cast to array types and will take the form of a single element list (null will still be null).</td></tr> |
| <tr><td>if</td><td>if(predicate,then,else) returns 'then' if 'predicate' evaluates to a positive number, otherwise it returns 'else'</td></tr> |
| <tr><td>nvl</td><td>nvl(expr,expr-for-null) returns 'expr-for-null' if 'expr' is null (or empty string for string type)</td></tr> |
| <tr><td>like</td><td>like(expr, pattern[, escape]) is equivalent to SQL <code>expr LIKE pattern</code></td></tr> |
| <tr><td>case_searched</td><td>case_searched(expr1, result1, [[expr2, result2, ...], else-result]) is similar to <code>CASE WHEN expr1 THEN result1 [ELSE else_result] END</code> in SQL</td></tr> |
| <tr><td>case_simple</td><td>case_simple(expr, value1, result1, [[value2, result2, ...], else-result]) is similar to <code>CASE expr WHEN value THEN result [ELSE else_result] END</code> in SQL</td></tr> |
| <tr><td>isnull</td><td>isnull(expr) returns 1 if the value is null, else 0</td></tr> |
| <tr><td>notnull</td><td>notnull(expr) returns 1 if the value is not null, else 0</td></tr> |
| <tr><td>bloom_filter_test</td><td>bloom_filter_test(expr, filter) tests the value of 'expr' against 'filter', a bloom filter serialized as a base64 string. See <a href="/docs/latest/development/extensions-core/bloom-filter.html">bloom filter extension</a> documentation for additional details.</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="string-functions"></a><a href="#string-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>String functions</h2> |
| <table> |
| <thead> |
| <tr><th>name</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>concat</td><td>concat(expr, expr...) concatenate a list of strings</td></tr> |
| <tr><td>format</td><td>format(pattern[, args...]) returns a string formatted in the manner of Java's <a href="https://docs.oracle.com/javase/8/docs/api/java/lang/String.html#format-java.lang.String-java.lang.Object...-">String.format</a>.</td></tr> |
| <tr><td>like</td><td>like(expr, pattern[, escape]) is equivalent to SQL <code>expr LIKE pattern</code></td></tr> |
| <tr><td>lookup</td><td>lookup(expr, lookup-name) looks up expr in a registered <a href="/docs/latest/querying/lookups.html">query-time lookup</a></td></tr> |
| <tr><td>parse_long</td><td>parse_long(string[, radix]) parses a string as a long with the given radix, or 10 (decimal) if a radix is not provided.</td></tr> |
| <tr><td>regexp_extract</td><td>regexp_extract(expr, pattern[, index]) applies a regular expression pattern and extracts a capture group index, or null if there is no match. If index is unspecified or zero, returns the substring that matched the pattern. The pattern may match anywhere inside <code>expr</code>; if you want to match the entire string instead, use the <code>^</code> and <code>$</code> markers at the start and end of your pattern.</td></tr> |
| <tr><td>regexp_like</td><td>regexp_like(expr, pattern) returns whether <code>expr</code> matches regular expression <code>pattern</code>. The pattern may match anywhere inside <code>expr</code>; if you want to match the entire string instead, use the <code>^</code> and <code>$</code> markers at the start and end of your pattern.</td></tr> |
| <tr><td>contains_string</td><td>contains_string(expr, string) returns whether <code>expr</code> contains <code>string</code> as a substring. This method is case-sensitive.</td></tr> |
| <tr><td>icontains_string</td><td>contains_string(expr, string) returns whether <code>expr</code> contains <code>string</code> as a substring. This method is case-insensitive.</td></tr> |
| <tr><td>replace</td><td>replace(expr, pattern, replacement) replaces pattern with replacement</td></tr> |
| <tr><td>substring</td><td>substring(expr, index, length) behaves like java.lang.String's substring</td></tr> |
| <tr><td>right</td><td>right(expr, length) returns the rightmost length characters from a string</td></tr> |
| <tr><td>left</td><td>left(expr, length) returns the leftmost length characters from a string</td></tr> |
| <tr><td>strlen</td><td>strlen(expr) returns length of a string in UTF-16 code units</td></tr> |
| <tr><td>strpos</td><td>strpos(haystack, needle[, fromIndex]) returns the position of the needle within the haystack, with indexes starting from 0. The search will begin at fromIndex, or 0 if fromIndex is not specified. If the needle is not found then the function returns -1.</td></tr> |
| <tr><td>trim</td><td>trim(expr[, chars]) remove leading and trailing characters from <code>expr</code> if they are present in <code>chars</code>. <code>chars</code> defaults to ' ' (space) if not provided.</td></tr> |
| <tr><td>ltrim</td><td>ltrim(expr[, chars]) remove leading characters from <code>expr</code> if they are present in <code>chars</code>. <code>chars</code> defaults to ' ' (space) if not provided.</td></tr> |
| <tr><td>rtrim</td><td>rtrim(expr[, chars]) remove trailing characters from <code>expr</code> if they are present in <code>chars</code>. <code>chars</code> defaults to ' ' (space) if not provided.</td></tr> |
| <tr><td>lower</td><td>lower(expr) converts a string to lowercase</td></tr> |
| <tr><td>upper</td><td>upper(expr) converts a string to uppercase</td></tr> |
| <tr><td>reverse</td><td>reverse(expr) reverses a string</td></tr> |
| <tr><td>repeat</td><td>repeat(expr, N) repeats a string N times</td></tr> |
| <tr><td>lpad</td><td>lpad(expr, length, chars) returns a string of <code>length</code> from <code>expr</code> left-padded with <code>chars</code>. If <code>length</code> is shorter than the length of <code>expr</code>, the result is <code>expr</code> which is truncated to <code>length</code>. The result will be null if either <code>expr</code> or <code>chars</code> is null. If <code>chars</code> is an empty string, no padding is added, however <code>expr</code> may be trimmed if necessary.</td></tr> |
| <tr><td>rpad</td><td>rpad(expr, length, chars) returns a string of <code>length</code> from <code>expr</code> right-padded with <code>chars</code>. If <code>length</code> is shorter than the length of <code>expr</code>, the result is <code>expr</code> which is truncated to <code>length</code>. The result will be null if either <code>expr</code> or <code>chars</code> is null. If <code>chars</code> is an empty string, no padding is added, however <code>expr</code> may be trimmed if necessary.</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="time-functions"></a><a href="#time-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Time functions</h2> |
| <table> |
| <thead> |
| <tr><th>name</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>timestamp</td><td>timestamp(expr[,format-string]) parses string expr into date then returns milliseconds from java epoch. without 'format-string' it's regarded as ISO datetime format</td></tr> |
| <tr><td>unix_timestamp</td><td>same with 'timestamp' function but returns seconds instead</td></tr> |
| <tr><td>timestamp_ceil</td><td>timestamp_ceil(expr, period, [origin, [timezone]]) rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".</td></tr> |
| <tr><td>timestamp_floor</td><td>timestamp_floor(expr, period, [origin, [timezone]]) rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".</td></tr> |
| <tr><td>timestamp_shift</td><td>timestamp_shift(expr, period, step, [timezone]) shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".</td></tr> |
| <tr><td>timestamp_extract</td><td>timestamp_extract(expr, unit, [timezone]) extracts a time part from expr, returning it as a number. Unit can be EPOCH (number of seconds since 1970-01-01 00:00:00 UTC), SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of <a href="https://en.wikipedia.org/wiki/ISO_week_date">week year</a>), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00"</td></tr> |
| <tr><td>timestamp_parse</td><td>timestamp_parse(string expr, [pattern, [timezone]]) parses a string into a timestamp using a given <a href="http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat">Joda DateTimeFormat pattern</a>. If the pattern is not provided, this parses time strings in either ISO8601 or SQL format. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as nulls.</td></tr> |
| <tr><td>timestamp_format</td><td>timestamp_format(expr, [pattern, [timezone]]) formats a timestamp as a string with a given <a href="http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat">Joda DateTimeFormat pattern</a>, or ISO8601 if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="math-functions"></a><a href="#math-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Math functions</h2> |
| <p>See javadoc of java.lang.Math for detailed explanation for each function.</p> |
| <table> |
| <thead> |
| <tr><th>name</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>abs</td><td>abs(x) returns the absolute value of x</td></tr> |
| <tr><td>acos</td><td>acos(x) returns the arc cosine of x</td></tr> |
| <tr><td>asin</td><td>asin(x) returns the arc sine of x</td></tr> |
| <tr><td>atan</td><td>atan(x) returns the arc tangent of x</td></tr> |
| <tr><td>bitwiseAnd</td><td>bitwiseAnd(x,y) returns the result of x & y. Double values will be implicitly cast to longs, use <code>bitwiseConvertDoubleToLongBits</code> to perform bitwise operations directly with doubles</td></tr> |
| <tr><td>bitwiseComplement</td><td>bitwiseComplement(x) returns the result of ~x. Double values will be implicitly cast to longs, use <code>bitwiseConvertDoubleToLongBits</code> to perform bitwise operations directly with doubles</td></tr> |
| <tr><td>bitwiseConvertDoubleToLongBits</td><td>bitwiseConvertDoubleToLongBits(x) converts the bits of an IEEE 754 floating-point double value to a long. If the input is not a double, it is implicitly cast to a double prior to conversion</td></tr> |
| <tr><td>bitwiseConvertLongBitsToDouble</td><td>bitwiseConvertLongBitsToDouble(x) converts a long to the IEEE 754 floating-point double specified by the bits stored in the long. If the input is not a long, it is implicitly cast to a long prior to conversion</td></tr> |
| <tr><td>bitwiseOr</td><td>bitwiseOr(x,y) returns the result of x [PIPE] y. Double values will be implicitly cast to longs, use <code>bitwiseConvertDoubleToLongBits</code> to perform bitwise operations directly with doubles</td></tr> |
| <tr><td>bitwiseShiftLeft</td><td>bitwiseShiftLeft(x,y) returns the result of x << y. Double values will be implicitly cast to longs, use <code>bitwiseConvertDoubleToLongBits</code> to perform bitwise operations directly with doubles</td></tr> |
| <tr><td>bitwiseShiftRight</td><td>bitwiseShiftRight(x,y) returns the result of x >> y. Double values will be implicitly cast to longs, use <code>bitwiseConvertDoubleToLongBits</code> to perform bitwise operations directly with doubles</td></tr> |
| <tr><td>bitwiseXor</td><td>bitwiseXor(x,y) returns the result of x ^ y. Double values will be implicitly cast to longs, use <code>bitwiseConvertDoubleToLongBits</code> to perform bitwise operations directly with doubles</td></tr> |
| <tr><td>atan2</td><td>atan2(y, x) returns the angle theta from the conversion of rectangular coordinates (x, y) to polar * coordinates (r, theta)</td></tr> |
| <tr><td>cbrt</td><td>cbrt(x) returns the cube root of x</td></tr> |
| <tr><td>ceil</td><td>ceil(x) returns the smallest (closest to negative infinity) double value that is greater than or equal to x and is equal to a mathematical integer</td></tr> |
| <tr><td>copysign</td><td>copysign(x) returns the first floating-point argument with the sign of the second floating-point argument</td></tr> |
| <tr><td>cos</td><td>cos(x) returns the trigonometric cosine of x</td></tr> |
| <tr><td>cosh</td><td>cosh(x) returns the hyperbolic cosine of x</td></tr> |
| <tr><td>cot</td><td>cot(x) returns the trigonometric cotangent of an angle x</td></tr> |
| <tr><td>div</td><td>div(x,y) is integer division of x by y</td></tr> |
| <tr><td>exp</td><td>exp(x) returns Euler's number raised to the power of x</td></tr> |
| <tr><td>expm1</td><td>expm1(x) returns e^x-1</td></tr> |
| <tr><td>floor</td><td>floor(x) returns the largest (closest to positive infinity) double value that is less than or equal to x and is equal to a mathematical integer</td></tr> |
| <tr><td>getExponent</td><td>getExponent(x) returns the unbiased exponent used in the representation of x</td></tr> |
| <tr><td>hypot</td><td>hypot(x, y) returns sqrt(x^2+y^2) without intermediate overflow or underflow</td></tr> |
| <tr><td>log</td><td>log(x) returns the natural logarithm of x</td></tr> |
| <tr><td>log10</td><td>log10(x) returns the base 10 logarithm of x</td></tr> |
| <tr><td>log1p</td><td>log1p(x) will the natural logarithm of x + 1</td></tr> |
| <tr><td>max</td><td>max(x, y) returns the greater of two values</td></tr> |
| <tr><td>min</td><td>min(x, y) returns the smaller of two values</td></tr> |
| <tr><td>nextafter</td><td>nextafter(x, y) returns the floating-point number adjacent to the x in the direction of the y</td></tr> |
| <tr><td>nextUp</td><td>nextUp(x) returns the floating-point value adjacent to x in the direction of positive infinity</td></tr> |
| <tr><td>pi</td><td>pi returns the constant value of the π</td></tr> |
| <tr><td>pow</td><td>pow(x, y) returns the value of the x raised to the power of y</td></tr> |
| <tr><td>remainder</td><td>remainder(x, y) returns the remainder operation on two arguments as prescribed by the IEEE 754 standard</td></tr> |
| <tr><td>rint</td><td>rint(x) returns value that is closest in value to x and is equal to a mathematical integer</td></tr> |
| <tr><td>round</td><td>round(x, y) returns the value of the x rounded to the y decimal places. While x can be an integer or floating-point number, y must be an integer. The type of the return value is specified by that of x. y defaults to 0 if omitted. When y is negative, x is rounded on the left side of the y decimal points. If x is <code>NaN</code>, x returns 0. If x is infinity, x will be converted to the nearest finite double.</td></tr> |
| <tr><td>safe_divide</td><td>safe_divide(x,y) returns the division of x by y if y is not equal to 0. In case y is 0 it returns 0 or <code>null</code> if <code>druid.generic.useDefaultValueForNull=false</code></td></tr> |
| <tr><td>scalb</td><td>scalb(d, sf) returns d * 2^sf rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set</td></tr> |
| <tr><td>signum</td><td>signum(x) returns the signum function of the argument x</td></tr> |
| <tr><td>sin</td><td>sin(x) returns the trigonometric sine of an angle x</td></tr> |
| <tr><td>sinh</td><td>sinh(x) returns the hyperbolic sine of x</td></tr> |
| <tr><td>sqrt</td><td>sqrt(x) returns the correctly rounded positive square root of x</td></tr> |
| <tr><td>tan</td><td>tan(x) returns the trigonometric tangent of an angle x</td></tr> |
| <tr><td>tanh</td><td>tanh(x) returns the hyperbolic tangent of x</td></tr> |
| <tr><td>todegrees</td><td>todegrees(x) converts an angle measured in radians to an approximately equivalent angle measured in degrees</td></tr> |
| <tr><td>toradians</td><td>toradians(x) converts an angle measured in degrees to an approximately equivalent angle measured in radians</td></tr> |
| <tr><td>ulp</td><td>ulp(x) returns the size of an ulp of the argument x</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="array-functions"></a><a href="#array-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Array functions</h2> |
| <table> |
| <thead> |
| <tr><th>function</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>array(expr1,expr ...)</td><td>constructs an array from the expression arguments, using the type of the first argument as the output array type</td></tr> |
| <tr><td>array_length(arr)</td><td>returns length of array expression</td></tr> |
| <tr><td>array_offset(arr,long)</td><td>returns the array element at the 0 based index supplied, or null for an out of range index</td></tr> |
| <tr><td>array_ordinal(arr,long)</td><td>returns the array element at the 1 based index supplied, or null for an out of range index</td></tr> |
| <tr><td>array_contains(arr,expr)</td><td>returns 1 if the array contains the element specified by expr, or contains all elements specified by expr if expr is an array, else 0</td></tr> |
| <tr><td>array_overlap(arr1,arr2)</td><td>returns 1 if arr1 and arr2 have any elements in common, else 0</td></tr> |
| <tr><td>array_offset_of(arr,expr)</td><td>returns the 0 based index of the first occurrence of expr in the array, or <code>-1</code> or <code>null</code> if <code>druid.generic.useDefaultValueForNull=false</code>if no matching elements exist in the array.</td></tr> |
| <tr><td>array_ordinal_of(arr,expr)</td><td>returns the 1 based index of the first occurrence of expr in the array, or <code>-1</code> or <code>null</code> if <code>druid.generic.useDefaultValueForNull=false</code> if no matching elements exist in the array.</td></tr> |
| <tr><td>array_prepend(expr,arr)</td><td>adds expr to arr at the beginning, the resulting array type determined by the type of the array</td></tr> |
| <tr><td>array_append(arr,expr)</td><td>appends expr to arr, the resulting array type determined by the type of the first array</td></tr> |
| <tr><td>array_concat(arr1,arr2)</td><td>concatenates 2 arrays, the resulting array type determined by the type of the first array</td></tr> |
| <tr><td>array_set_add(arr,expr)</td><td>adds expr to arr and converts the array to a new array composed of the unique set of elements. The resulting array type determined by the type of the array</td></tr> |
| <tr><td>array_set_add_all(arr1,arr2)</td><td>combines the unique set of elements of 2 arrays, the resulting array type determined by the type of the first array</td></tr> |
| <tr><td>array_slice(arr,start,end)</td><td>return the subarray of arr from the 0 based index start(inclusive) to end(exclusive), or <code>null</code>, if start is less than 0, greater than length of arr or less than end</td></tr> |
| <tr><td>array_to_string(arr,str)</td><td>joins all elements of arr by the delimiter specified by str</td></tr> |
| <tr><td>string_to_array(str1,str2)</td><td>splits str1 into an array on the delimiter specified by str2</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="apply-functions"></a><a href="#apply-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Apply functions</h2> |
| <p>Apply functions allow for special 'lambda' expressions to be defined and applied to array inputs to enable free-form transformations.</p> |
| <table> |
| <thead> |
| <tr><th>function</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>map(lambda,arr)</td><td>applies a transform specified by a single argument lambda expression to all elements of arr, returning a new array</td></tr> |
| <tr><td>cartesian_map(lambda,arr1,arr2,...)</td><td>applies a transform specified by a multi argument lambda expression to all elements of the Cartesian product of all input arrays, returning a new array; the number of lambda arguments and array inputs must be the same</td></tr> |
| <tr><td>filter(lambda,arr)</td><td>filters arr by a single argument lambda, returning a new array with all matching elements, or null if no elements match</td></tr> |
| <tr><td>fold(lambda,arr)</td><td>folds a 2 argument lambda across arr. The first argument of the lambda is the array element and the second the accumulator, returning a single accumulated value.</td></tr> |
| <tr><td>cartesian_fold(lambda,arr1,arr2,...)</td><td>folds a multi argument lambda across the Cartesian product of all input arrays. The first arguments of the lambda is the array element and the last is the accumulator, returning a single accumulated value.</td></tr> |
| <tr><td>any(lambda,arr)</td><td>returns 1 if any element in the array matches the lambda expression, else 0</td></tr> |
| <tr><td>all(lambda,arr)</td><td>returns 1 if all elements in the array matches the lambda expression, else 0</td></tr> |
| </tbody> |
| </table> |
| <h3><a class="anchor" aria-hidden="true" id="lambda-expressions-syntax"></a><a href="#lambda-expressions-syntax" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Lambda expressions syntax</h3> |
| <p>Lambda expressions are a sort of function definition, where new identifiers can be defined and passed as input to the expression body</p> |
| <pre><code class="hljs"><span class="hljs-function"><span class="hljs-params">(identifier1 ...)</span> -></span> expr |
| </code></pre> |
| <p>e.g.</p> |
| <pre><code class="hljs">(<span class="hljs-symbol">x</span>, <span class="hljs-symbol">y</span>) -> <span class="hljs-symbol">x</span> + <span class="hljs-symbol">y</span> |
| </code></pre> |
| <p>The identifier arguments of a lambda expression correspond to the elements of the array it is being applied to. For example:</p> |
| <pre><code class="hljs"><span class="hljs-keyword">map</span><span class="hljs-function"><span class="hljs-params">((x) -> x + <span class="hljs-number">1</span>, some_multi_value_column)</span> |
| </span></code></pre> |
| <p>will map each element of <code>some_multi_value_column</code> to the identifier <code>x</code> so that the lambda expression body can be evaluated for each <code>x</code>. The scoping rules are that lambda arguments will override identifiers which are defined externally from the lambda expression body. Using the same example:</p> |
| <pre><code class="hljs"><span class="hljs-keyword">map</span><span class="hljs-function"><span class="hljs-params">((x) -> x + <span class="hljs-number">1</span>, x)</span> |
| </span></code></pre> |
| <p>in this case, the <code>x</code> when evaluating <code>x + 1</code> is the lambda argument, thus an element of the multi-valued column <code>x</code>, rather than the column <code>x</code> itself.</p> |
| <h2><a class="anchor" aria-hidden="true" id="reduction-functions"></a><a href="#reduction-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Reduction functions</h2> |
| <p>Reduction functions operate on zero or more expressions and return a single expression. If no expressions are passed as |
| arguments, then the result is <code>NULL</code>. The expressions must all be convertible to a common data type, which will be the |
| type of the result:</p> |
| <ul> |
| <li>If all arguments are <code>NULL</code>, the result is <code>NULL</code>. Otherwise, <code>NULL</code> arguments are ignored.</li> |
| <li>If the arguments comprise a mix of numbers and strings, the arguments are interpreted as strings.</li> |
| <li>If all arguments are integer numbers, the arguments are interpreted as longs.</li> |
| <li>If all arguments are numbers and at least one argument is a double, the arguments are interpreted as doubles.</li> |
| </ul> |
| <table> |
| <thead> |
| <tr><th>function</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>greatest([expr1, ...])</td><td>Evaluates zero or more expressions and returns the maximum value based on comparisons as described above.</td></tr> |
| <tr><td>least([expr1, ...])</td><td>Evaluates zero or more expressions and returns the minimum value based on comparisons as described above.</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="ip-address-functions"></a><a href="#ip-address-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>IP address functions</h2> |
| <p>For the IPv4 address functions, the <code>address</code> argument accepts either an IPv4 dotted-decimal string (e.g., "192.168.0.1") or an IP address represented as a long (e.g., 3232235521). Format the <code>subnet</code> argument as an IPv4 address subnet in CIDR notation (e.g., "192.168.0.0/16").</p> |
| <table> |
| <thead> |
| <tr><th>function</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>ipv4_match(address, subnet)</td><td>Returns 1 if the <code>address</code> belongs to the <code>subnet</code> literal, else 0. If <code>address</code> is not a valid IPv4 address, then 0 is returned. This function is more efficient if <code>address</code> is a long instead of a string.</td></tr> |
| <tr><td>ipv4_parse(address)</td><td>Parses <code>address</code> into an IPv4 address stored as a long. Returns <code>address</code> if it is already a valid IPv4 integer address. Returns null if <code>address</code> cannot be represented as an IPv4 address.</td></tr> |
| <tr><td>ipv4_stringify(address)</td><td>Converts <code>address</code> into an IPv4 address dotted-decimal string. Returns <code>address</code> if it is already a valid IPv4 dotted-decimal string. Returns null if <code>address</code> cannot be represented as an IPv4 address.</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="other-functions"></a><a href="#other-functions" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Other functions</h2> |
| <table> |
| <thead> |
| <tr><th>function</th><th>description</th></tr> |
| </thead> |
| <tbody> |
| <tr><td>human_readable_binary_byte_format(value[, precision])</td><td>Format a number in human-readable <a href="https://en.wikipedia.org/wiki/Binary_prefix">IEC</a> format. <code>precision</code> must be in the range of [0,3] (default: 2). For example:<li> human_readable_binary_byte_format(1048576) returns <code>1.00 MiB</code></li><li>human_readable_binary_byte_format(1048576, 3) returns <code>1.000 MiB</code></li></td></tr> |
| <tr><td>human_readable_decimal_byte_format(value[, precision])</td><td>Format a number in human-readable <a href="https://en.wikipedia.org/wiki/Binary_prefix">SI</a> format. <code>precision</code> must be in the range of [0,3] (default: 2). For example:<li> human_readable_decimal_byte_format(1000000) returns <code>1.00 MB</code></li><li>human_readable_decimal_byte_format(1000000, 3) returns <code>1.000 MB</code></li></td></tr> |
| <tr><td>human_readable_decimal_format(value[, precision])</td><td>Format a number in human-readable SI format. <code>precision</code> must be in the range of [0,3] (default: 2). For example:<li>human_readable_decimal_format(1000000) returns <code>1.00 M</code></li><li>human_readable_decimal_format(1000000, 3) returns <code>1.000 M</code></li></td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="vectorization-support"></a><a href="#vectorization-support" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Vectorization Support</h2> |
| <p>A number of expressions support <a href="/docs/latest/querying/query-context.html#vectorization-parameters">'vectorized' query engines</a></p> |
| <p>supported features:</p> |
| <ul> |
| <li>constants and identifiers are supported for any column type</li> |
| <li><code>cast</code> is supported for numeric and string types</li> |
| <li>math operators: <code>+</code>,<code>-</code>,<code>*</code>,<code>/</code>,<code>%</code>,<code>^</code> are supported for numeric types</li> |
| <li>logical operators: <code>!</code>, <code>&&</code>, <code>||</code>, are supported for string and numeric types (if <code>druid.expressions.useStrictBooleans=true</code>)</li> |
| <li>comparison operators: <code>=</code>, <code>!=</code>, <code>></code>, <code>>=</code>, <code><</code>, <code><=</code> are supported for string and numeric types</li> |
| <li>math functions: <code>abs</code>, <code>acos</code>, <code>asin</code>, <code>atan</code>, <code>cbrt</code>, <code>ceil</code>, <code>cos</code>, <code>cosh</code>, <code>cot</code>, <code>exp</code>, <code>expm1</code>, <code>floor</code>, <code>getExponent</code>, <code>log</code>, <code>log10</code>, <code>log1p</code>, <code>nextUp</code>, <code>rint</code>, <code>signum</code>, <code>sin</code>, <code>sinh</code>, <code>sqrt</code>, <code>tan</code>, <code>tanh</code>, <code>toDegrees</code>, <code>toRadians</code>, <code>ulp</code>, <code>atan2</code>, <code>copySign</code>, <code>div</code>, <code>hypot</code>, <code>max</code>, <code>min</code>, <code>nextAfter</code>, <code>pow</code>, <code>remainder</code>, <code>scalb</code> are supported for numeric types</li> |
| <li>time functions: <code>timestamp_floor</code> (with constant granularity argument) is supported for numeric types</li> |
| <li>boolean functions: <code>isnull</code>, <code>notnull</code> are supported for string and numeric types</li> |
| <li>conditional functions: <code>nvl</code> is supported for string and numeric types</li> |
| <li>string functions: the concatenation operator (<code>+</code>) and <code>concat</code> function are supported for string and numeric types</li> |
| <li>other: <code>parse_long</code> is supported for numeric and string types</li> |
| </ul> |
| <h2><a class="anchor" aria-hidden="true" id="logical-operator-modes"></a><a href="#logical-operator-modes" aria-hidden="true" class="hash-link"><svg class="hash-link-icon" aria-hidden="true" height="16" version="1.1" viewBox="0 0 16 16" width="16"><path fill-rule="evenodd" d="M4 9h1v1H4c-1.5 0-3-1.69-3-3.5S2.55 3 4 3h4c1.45 0 3 1.69 3 3.5 0 1.41-.91 2.72-2 3.25V8.59c.58-.45 1-1.27 1-2.09C10 5.22 8.98 4 8 4H4c-.98 0-2 1.22-2 2.5S3 9 4 9zm9-3h-1v1h1c1 0 2 1.22 2 2.5S13.98 12 13 12H9c-.98 0-2-1.22-2-2.5 0-.83.42-1.64 1-2.09V6.25c-1.09.53-2 1.84-2 3.25C6 11.31 7.55 13 9 13h4c1.45 0 3-1.69 3-3.5S14.5 6 13 6z"></path></svg></a>Logical operator modes</h2> |
| <p>Prior to the 0.23 release of Apache Druid, boolean function expressions have inconsistent handling of true and false values, and the logical 'and' and 'or' operators behave in a manner that is incompatible with SQL, even if SQL compatible null handling mode (<code>druid.generic.useDefaultValueForNull=false</code>) is enabled. Logical operators also pass through their input values similar to many scripting languages, and treat <code>null</code> as false, which can result in some rather strange behavior. Other boolean operations, such as comparisons and equality, retain their input types (e.g. <code>DOUBLE</code> comparison would produce <code>1.0</code> for true and <code>0.0</code> for false), while many other boolean functions strictly produce <code>LONG</code> typed values of <code>1</code> for true and <code>0</code> for false.</p> |
| <p>After 0.23, while the inconsistent legacy behavior is still the default, it can be optionally be changed by setting <code>druid.expressions.useStrictBooleans=true</code>, so that these operations will allow correctly treating <code>null</code> values as "unknown" for SQL compatible behavior, and <em>all boolean output functions</em> will output 'homogeneous' <code>LONG</code> typed boolean values of <code>1</code> for <code>true</code> and <code>0</code> for <code>false</code>. Additionally,</p> |
| <p>For the "or" operator:</p> |
| <ul> |
| <li><code>true || null</code>, <code>null || true</code>, -> <code>1</code></li> |
| <li><code>false || null</code>, <code>null || false</code>, <code>null || null</code>-> <code>null</code></li> |
| </ul> |
| <p>For the "and" operator:</p> |
| <ul> |
| <li><code>true && null</code>, <code>null && true</code>, <code>null && null</code> -> <code>null</code></li> |
| <li><code>false && null</code>, <code>null && false</code> -> <code>0</code></li> |
| </ul> |
| <p>Druid currently still retains implicit conversion of <code>LONG</code>, <code>DOUBLE</code>, and <code>STRING</code> types into boolean values in both modes:</p> |
| <ul> |
| <li><code>LONG</code> or <code>DOUBLE</code> - any value greater than 0 is considered <code>true</code>, else <code>false</code></li> |
| <li><code>STRING</code> - the value <code>'true'</code> (case insensitive) is considered <code>true</code>, everything else is <code>false</code>.</li> |
| </ul> |
| <p>Legacy behavior:</p> |
| <ul> |
| <li><code>100 && 11</code> -> <code>11</code></li> |
| <li><code>0.7 || 0.3</code> -> <code>0.3</code></li> |
| <li><code>100 && 0</code> -> <code>0</code></li> |
| <li><code>'troo' && 'true'</code> -> <code>'troo'</code></li> |
| <li><code>'troo' || 'true'</code> -> <code>'true'</code></li> |
| </ul> |
| <p>SQL compatible behavior:</p> |
| <ul> |
| <li><code>100 && 11</code> -> <code>1</code></li> |
| <li><code>0.7 || 0.3</code> -> <code>1</code></li> |
| <li><code>100 && 0</code> -> <code>0</code></li> |
| <li><code>'troo' && 'true'</code> -> <code>0</code></li> |
| <li><code>'troo' || 'true'</code> -> <code>1</code></li> |
| </ul> |
| </span></div></article></div><div class="docs-prevnext"><a class="docs-prev button" href="/docs/latest/querying/post-aggregations.html"><span class="arrow-prev">← </span><span>Post-aggregations</span></a><a class="docs-next button" href="/docs/latest/querying/having.html"><span class="function-name-prevnext">Having filters (groupBy)</span><span class="arrow-next"> →</span></a></div></div></div><nav class="onPageNav"><ul class="toc-headings"><li><a href="#general-functions">General functions</a></li><li><a href="#string-functions">String functions</a></li><li><a href="#time-functions">Time functions</a></li><li><a href="#math-functions">Math functions</a></li><li><a href="#array-functions">Array functions</a></li><li><a href="#apply-functions">Apply functions</a><ul class="toc-headings"><li><a href="#lambda-expressions-syntax">Lambda expressions syntax</a></li></ul></li><li><a href="#reduction-functions">Reduction functions</a></li><li><a href="#ip-address-functions">IP address functions</a></li><li><a href="#other-functions">Other functions</a></li><li><a href="#vectorization-support">Vectorization Support</a></li><li><a href="#logical-operator-modes">Logical operator modes</a></li></ul></nav></div><footer class="nav-footer druid-footer" id="footer"><div class="container"><div class="text-center"><p><a href="/technology">Technology</a> · <a href="/use-cases">Use Cases</a> · <a href="/druid-powered">Powered by Druid</a> · <a href="/docs/latest/latest">Docs</a> · <a href="/community/">Community</a> · <a href="/downloads.html">Download</a> · <a href="/faq">FAQ</a></p></div><div class="text-center"><a title="Join the user group" href="https://groups.google.com/forum/#!forum/druid-user" target="_blank"><span class="fa fa-comments"></span></a> · <a title="Follow Druid" href="https://twitter.com/druidio" target="_blank"><span class="fab fa-twitter"></span></a> · <a title="Download via Apache" href="https://www.apache.org/dyn/closer.cgi?path=/incubator/druid/{{ site.druid_versions[0].versions[0].version }}/apache-druid-{{ site.druid_versions[0].versions[0].version }}-bin.tar.gz" target="_blank"><span class="fas fa-feather"></span></a> · <a title="GitHub" href="https://github.com/apache/druid" target="_blank"><span class="fab fa-github"></span></a></div><div class="text-center license">Copyright © 2019 <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a>.<br/>Except where otherwise noted, licensed under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC BY-SA 4.0</a>.<br/>Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.</div></div></footer></div><script type="text/javascript" src="https://cdn.jsdelivr.net/docsearch.js/1/docsearch.min.js"></script><script> |
| document.addEventListener('keyup', function(e) { |
| if (e.target !== document.body) { |
| return; |
| } |
| // keyCode for '/' (slash) |
| if (e.keyCode === 191) { |
| const search = document.getElementById('search_input_react'); |
| search && search.focus(); |
| } |
| }); |
| </script><script> |
| var search = docsearch({ |
| |
| apiKey: '2de99082a9f38e49dfaa059bbe4c901d', |
| indexName: 'apache_druid', |
| inputSelector: '#search_input_react', |
| algoliaOptions: {"facetFilters":["language:en","version:0.23.0"]} |
| }); |
| </script></body></html> |
