| <!DOCTYPE html><html lang="en"><head><meta charSet="utf-8"/><meta http-equiv="X-UA-Compatible" content="IE=edge"/><title>SQL scalar functions · Apache Druid</title><meta name="viewport" content="width=device-width, initial-scale=1.0"/><link rel="canonical" href="https://druid.apache.org/docs/latest/querying/sql-scalar.html"/><meta name="generator" content="Docusaurus"/><meta name="description" content="<!--"/><meta name="docsearch:language" content="en"/><meta name="docsearch:version" content="26.0.0" /><meta property="og:title" content="SQL scalar functions · 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/npm/docsearch.js@2/dist/cdn/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>Druid SQL</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 (local)</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">Load files natively</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-msq-extern.html">Load files using SQL 🆕</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-sketches-theta.html">Theta sketches</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/docker.html">Tutorial: Run with Docker</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-kerberos-hadoop.html">Kerberized HDFS deep storage</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-msq-convert-spec.html">Convert ingestion spec to SQL</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-sql-query-view.html">Get to know Query view</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-unnest-arrays.html">Unnesting arrays</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-jupyter-index.html">Jupyter Notebook tutorials</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-jupyter-docker.html">Docker for tutorials</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/tutorials/tutorial-jdbc.html">JDBC connector</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><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-input-sources.html">Native batch: input sources</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/migrate-from-firehose.html">Migrate from firehose</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/hadoop.html">Hadoop-based</a></li></ul></div><div class="navGroup subNavGroup"><h4 class="navGroupSubcategoryTitle">SQL-based ingestion 🆕</h4><ul><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/index.html">Overview</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/concepts.html">Key concepts</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/api.html">API</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/security.html">Security</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/examples.html">Examples</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/reference.html">Reference</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/multi-stage-query/known-issues.html">Known issues</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">Data management<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/data-management/index.html">Overview</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/update.html">Data updates</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/delete.html">Data deletion</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/schema-changes.html">Schema changes</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/compaction.html">Compaction</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/data-management/automatic-compaction.html">Automatic compaction</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 navListItemActive"><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-json-functions.html">JSON functions</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/querying/sql-functions.html">All 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/nested-columns.html">Nested columns</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"><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/web-console.html">Web console</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/operations/java.html">Java runtime</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">Using rules to drop and retain 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/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/native-batch-firehose.html">Firehose (deprecated)</a></li><li class="navListItem"><a class="navItem" href="/docs/latest/ingestion/native-batch-simple-task.html">Native batch (simple)</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/querying/sql-scalar.md" target="_blank" rel="noreferrer noopener">Edit</a><h1 id="__docusaurus" class="postHeaderTitle">SQL scalar functions</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. |
| --> |
| <!-- |
| The format of the tables that describe the functions and operators |
| should not be changed without updating the script create-sql-docs |
| in web-console/script/create-sql-docs, because the script detects |
| patterns in this markdown file and parse it to TypeScript file for web console |
| --> |
| <blockquote> |
| <p>Apache Druid supports two query languages: Druid SQL and <a href="/docs/latest/querying/querying.html">native queries</a>. |
| This document describes the SQL language.</p> |
| </blockquote> |
| <p><a href="/docs/latest/querying/sql.html">Druid SQL</a> includes scalar functions that include numeric and string functions, IP address functions, Sketch functions, and more, as described on this page.</p> |
| <h2><a class="anchor" aria-hidden="true" id="numeric-functions"></a><a href="#numeric-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>Numeric functions</h2> |
| <p>For mathematical operations, Druid SQL will use integer math if all operands involved in an expression are integers. |
| Otherwise, Druid will switch to floating point math. You can force this to happen by casting one of your operands |
| to FLOAT. At runtime, Druid will widen 32-bit floats to 64-bit for most expressions.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>PI</code></td><td>Constant Pi.</td></tr> |
| <tr><td><code>ABS(expr)</code></td><td>Absolute value.</td></tr> |
| <tr><td><code>CEIL(expr)</code></td><td>Ceiling.</td></tr> |
| <tr><td><code>EXP(expr)</code></td><td>e to the power of <code>expr</code>.</td></tr> |
| <tr><td><code>FLOOR(expr)</code></td><td>Floor.</td></tr> |
| <tr><td><code>LN(expr)</code></td><td>Logarithm (base e).</td></tr> |
| <tr><td><code>LOG10(expr)</code></td><td>Logarithm (base 10).</td></tr> |
| <tr><td><code>POWER(expr, power)</code></td><td><code>expr</code> raised to the power of <code>power</code>.</td></tr> |
| <tr><td><code>SQRT(expr)</code></td><td>Square root.</td></tr> |
| <tr><td><code>TRUNCATE(expr, [digits])</code></td><td>Truncate <code>expr</code> to a specific number of decimal digits. If digits is negative, then this truncates that many places to the left of the decimal point. Digits defaults to zero if not specified.</td></tr> |
| <tr><td><code>TRUNC(expr, [digits])</code></td><td>Alias for <code>TRUNCATE</code>.</td></tr> |
| <tr><td><code>ROUND(expr, [digits])</code></td><td><code>ROUND(x, y)</code> would return 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 <code>expr</code> evaluates to either <code>NaN</code>, <code>expr</code> will be converted to 0. If <code>expr</code> is infinity, <code>expr</code> will be converted to the nearest finite double.</td></tr> |
| <tr><td><code>MOD(x, y)</code></td><td>Modulo (remainder of x divided by y).</td></tr> |
| <tr><td><code>SIN(expr)</code></td><td>Trigonometric sine of an angle <code>expr</code>.</td></tr> |
| <tr><td><code>COS(expr)</code></td><td>Trigonometric cosine of an angle <code>expr</code>.</td></tr> |
| <tr><td><code>TAN(expr)</code></td><td>Trigonometric tangent of an angle <code>expr</code>.</td></tr> |
| <tr><td><code>COT(expr)</code></td><td>Trigonometric cotangent of an angle <code>expr</code>.</td></tr> |
| <tr><td><code>ASIN(expr)</code></td><td>Arc sine of <code>expr</code>.</td></tr> |
| <tr><td><code>ACOS(expr)</code></td><td>Arc cosine of <code>expr</code>.</td></tr> |
| <tr><td><code>ATAN(expr)</code></td><td>Arc tangent of <code>expr</code>.</td></tr> |
| <tr><td><code>ATAN2(y, x)</code></td><td>Angle theta from the conversion of rectangular coordinates (x, y) to polar * coordinates (r, theta).</td></tr> |
| <tr><td><code>DEGREES(expr)</code></td><td>Converts an angle measured in radians to an approximately equivalent angle measured in degrees.</td></tr> |
| <tr><td><code>RADIANS(expr)</code></td><td>Converts an angle measured in degrees to an approximately equivalent angle measured in radians.</td></tr> |
| <tr><td><code>BITWISE_AND(expr1, expr2)</code></td><td>Returns the result of <code>expr1 & expr2</code>. Double values will be implicitly cast to longs, use <code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS</code> to perform bitwise operations directly with doubles.</td></tr> |
| <tr><td><code>BITWISE_COMPLEMENT(expr)</code></td><td>Returns the result of <code>~expr</code>. Double values will be implicitly cast to longs, use <code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS</code> to perform bitwise operations directly with doubles.</td></tr> |
| <tr><td><code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(expr)</code></td><td>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><code>BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(expr)</code></td><td>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><code>BITWISE_OR(expr1, expr2)</code></td><td>Returns the result of <code>expr1 [PIPE] expr2</code>. Double values will be implicitly cast to longs, use <code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS</code> to perform bitwise operations directly with doubles.</td></tr> |
| <tr><td><code>BITWISE_SHIFT_LEFT(expr1, expr2)</code></td><td>Returns the result of <code>expr1 << expr2</code>. Double values will be implicitly cast to longs, use <code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS</code> to perform bitwise operations directly with doubles.</td></tr> |
| <tr><td><code>BITWISE_SHIFT_RIGHT(expr1, expr2)</code></td><td>Returns the result of <code>expr1 >> expr2</code>. Double values will be implicitly cast to longs, use <code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS</code> to perform bitwise operations directly with doubles.</td></tr> |
| <tr><td><code>BITWISE_XOR(expr1, expr2)</code></td><td>Returns the result of <code>expr1 ^ expr2</code>. Double values will be implicitly cast to longs, use <code>BITWISE_CONVERT_DOUBLE_TO_LONG_BITS</code> to perform bitwise operations directly with doubles.</td></tr> |
| <tr><td><code>DIV(x, y)</code></td><td>Returns the result of integer division of x by y</td></tr> |
| <tr><td><code>HUMAN_READABLE_BINARY_BYTE_FORMAT(value, [precision])</code></td><td>Format a number in human-readable <a href="https://en.wikipedia.org/wiki/Binary_prefix">IEC</a> format. For example, HUMAN_READABLE_BINARY_BYTE_FORMAT(1048576) returns <code>1.00 MiB</code>. <code>precision</code> must be in the range of <code>[0, 3]</code> (default: 2).</td></tr> |
| <tr><td><code>HUMAN_READABLE_DECIMAL_BYTE_FORMAT(value, [precision])</code></td><td>Format a number in human-readable <a href="https://en.wikipedia.org/wiki/Binary_prefix">SI</a> format. HUMAN_READABLE_DECIMAL_BYTE_FORMAT(1048576) returns <code>1.04 MB</code>. <code>precision</code> must be in the range of <code>[0, 3]</code> (default: 2). <code>precision</code> must be in the range of <code>[0, 3]</code> (default: 2).</td></tr> |
| <tr><td><code>HUMAN_READABLE_DECIMAL_FORMAT(value, [precision])</code></td><td>Format a number in human-readable SI format. For example, HUMAN_READABLE_DECIMAL_FORMAT(1048576) returns <code>1.04 M</code>. <code>precision</code> must be in the range of <code>[0, 3]</code> (default: 2).</td></tr> |
| <tr><td><code>SAFE_DIVIDE(x, y)</code></td><td>Returns the division of x by y guarded on division by 0. In case y is 0 it returns 0, or <code>null</code> if <code>druid.generic.useDefaultValueForNull=false</code></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> |
| <p>String functions accept strings, and return a type appropriate to the function.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>CONCAT(expr, expr...)</code></td><td>Concats a list of expressions. Also see the <a href="/docs/latest/querying/sql-operators.html#concatenation-operator">concatenation operator</a>.</td></tr> |
| <tr><td><code>TEXTCAT(expr, expr)</code></td><td>Two argument version of <code>CONCAT</code>.</td></tr> |
| <tr><td><code>STRING_FORMAT(pattern, [args...])</code></td><td>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><code>LENGTH(expr)</code></td><td>Length of <code>expr</code> in UTF-16 code units.</td></tr> |
| <tr><td><code>CHAR_LENGTH(expr)</code></td><td>Alias for <code>LENGTH</code>.</td></tr> |
| <tr><td><code>CHARACTER_LENGTH(expr)</code></td><td>Alias for <code>LENGTH</code>.</td></tr> |
| <tr><td><code>STRLEN(expr)</code></td><td>Alias for <code>LENGTH</code>.</td></tr> |
| <tr><td><code>LOOKUP(expr, lookupName)</code></td><td>Look up <code>expr</code> in a registered <a href="/docs/latest/querying/lookups.html">query-time lookup table</a>. Note that lookups can also be queried directly using the <a href="/docs/latest/querying/sql.html#from"><code>lookup</code> schema</a>.</td></tr> |
| <tr><td><code>LOWER(expr)</code></td><td>Returns <code>expr</code> in all lowercase.</td></tr> |
| <tr><td><code>UPPER(expr)</code></td><td>Returns <code>expr</code> in all uppercase.</td></tr> |
| <tr><td><code>PARSE_LONG(string, [radix])</code></td><td>Parses a string into a long (BIGINT) with the given radix, or 10 (decimal) if a radix is not provided.</td></tr> |
| <tr><td><code>POSITION(needle IN haystack [FROM fromIndex])</code></td><td>Returns the index of <code>needle</code> within <code>haystack</code>, with indexes starting from 1. The search will begin at <code>fromIndex</code>, or 1 if <code>fromIndex</code> is not specified. If <code>needle</code> is not found, returns 0.</td></tr> |
| <tr><td><code>REGEXP_EXTRACT(expr, pattern, [index])</code></td><td>Apply regular expression <code>pattern</code> to <code>expr</code> and extract a capture group, or <code>NULL</code> if there is no match. If index is unspecified or zero, returns the first 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. Note: when <code>druid.generic.useDefaultValueForNull = true</code>, it is not possible to differentiate an empty-string match from a non-match (both will return <code>NULL</code>).</td></tr> |
| <tr><td><code>REGEXP_LIKE(expr, pattern)</code></td><td>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. Similar to <a href="/docs/latest/querying/sql-operators.html#logical-operators"><code>LIKE</code></a>, but uses regexps instead of LIKE patterns. Especially useful in WHERE clauses.</td></tr> |
| <tr><td><code>CONTAINS_STRING(expr, str)</code></td><td>Returns true if the <code>str</code> is a substring of <code>expr</code>.</td></tr> |
| <tr><td><code>ICONTAINS_STRING(expr, str)</code></td><td>Returns true if the <code>str</code> is a substring of <code>expr</code>. The match is case-insensitive.</td></tr> |
| <tr><td><code>REPLACE(expr, pattern, replacement)</code></td><td>Replaces pattern with replacement in <code>expr</code>, and returns the result.</td></tr> |
| <tr><td><code>STRPOS(haystack, needle)</code></td><td>Returns the index of <code>needle</code> within <code>haystack</code>, with indexes starting from 1. If <code>needle</code> is not found, returns 0.</td></tr> |
| <tr><td><code>SUBSTRING(expr, index, [length])</code></td><td>Returns a substring of <code>expr</code> starting at index, with a max length, both measured in UTF-16 code units.</td></tr> |
| <tr><td><code>RIGHT(expr, [length])</code></td><td>Returns the rightmost length characters from <code>expr</code>.</td></tr> |
| <tr><td><code>LEFT(expr, [length])</code></td><td>Returns the leftmost length characters from <code>expr</code>.</td></tr> |
| <tr><td><code>SUBSTR(expr, index, [length])</code></td><td>Alias for <code>SUBSTRING</code>.</td></tr> |
| <tr><td><code>TRIM([BOTH</code><code>|</code><code>LEADING</code><code>|</code><code>TRAILING] [chars FROM] expr)</code></td><td>Returns <code>expr</code> with characters removed from the leading, trailing, or both ends of "expr" if they are in "chars". If "chars" is not provided, it defaults to " " (a space). If the directional argument is not provided, it defaults to "BOTH".</td></tr> |
| <tr><td><code>BTRIM(expr, [chars])</code></td><td>Alternate form of <code>TRIM(BOTH chars FROM expr)</code>.</td></tr> |
| <tr><td><code>LTRIM(expr, [chars])</code></td><td>Alternate form of <code>TRIM(LEADING chars FROM expr)</code>.</td></tr> |
| <tr><td><code>RTRIM(expr, [chars])</code></td><td>Alternate form of <code>TRIM(TRAILING chars FROM expr)</code>.</td></tr> |
| <tr><td><code>REVERSE(expr)</code></td><td>Reverses <code>expr</code>.</td></tr> |
| <tr><td><code>REPEAT(expr, [N])</code></td><td>Repeats <code>expr</code> N times</td></tr> |
| <tr><td><code>LPAD(expr, length, [chars])</code></td><td>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><code>RPAD(expr, length, [chars])</code></td><td>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="date-and-time-functions"></a><a href="#date-and-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>Date and time functions</h2> |
| <p>Time functions can be used with:</p> |
| <ul> |
| <li>Druid's primary timestamp column, <code>__time</code>;</li> |
| <li>Numeric values representing milliseconds since the epoch, through the MILLIS_TO_TIMESTAMP function; and</li> |
| <li>String timestamps, through the TIME_PARSE function.</li> |
| </ul> |
| <p>By default, time operations use the UTC time zone. You can change the time zone by setting the connection |
| context parameter <code>sqlTimeZone</code> to the name of another time zone, like <code>America/Los_Angeles</code>, or to an offset like |
| <code>-08:00</code>. If you need to mix multiple time zones in the same query, or if you need to use a time zone other than |
| the connection time zone, some functions also accept time zones as parameters. These parameters always take precedence |
| over the connection time zone.</p> |
| <p>Literal timestamps in the connection time zone can be written using <code>TIMESTAMP '2000-01-01 00:00:00'</code> syntax. The |
| simplest way to write literal timestamps in other time zones is to use TIME_PARSE, like |
| <code>TIME_PARSE('2000-02-01 00:00:00', NULL, 'America/Los_Angeles')</code>.</p> |
| <p>The best ways to filter based on time are by using ISO8601 intervals, like |
| <code>TIME_IN_INTERVAL(__time, '2000-01-01/2000-02-01')</code>, or by using literal timestamps with the <code>>=</code> and <code><</code> operators, like |
| <code>__time >= TIMESTAMP '2000-01-01 00:00:00' AND __time < TIMESTAMP '2000-02-01 00:00:00'</code>.</p> |
| <p>Druid supports the standard SQL BETWEEN operator, but we recommend avoiding it for time filters. BETWEEN is inclusive |
| of its upper bound, which makes it awkward to write time filters correctly. For example, the equivalent of |
| <code>TIME_IN_INTERVAL(__time, '2000-01-01/2000-02-01')</code> is |
| <code>__time BETWEEN TIMESTAMP '2000-01-01 00:00:00' AND TIMESTAMP '2000-01-31 23:59:59.999'</code>.</p> |
| <p>Druid processes timestamps internally as longs (64-bit integers) representing milliseconds since the epoch. Therefore, |
| time functions perform best when used with the primary timestamp column, or with timestamps stored in long columns as |
| milliseconds and accessed with MILLIS_TO_TIMESTAMP. Other timestamp representations, include string timestamps and |
| POSIX timestamps (seconds since the epoch) require query-time conversion to Druid's internal form, which adds additional |
| overhead.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>CURRENT_TIMESTAMP</code></td><td>Current timestamp in the connection's time zone.</td></tr> |
| <tr><td><code>CURRENT_DATE</code></td><td>Current date in the connection's time zone.</td></tr> |
| <tr><td><code>DATE_TRUNC(unit, timestamp_expr)</code></td><td>Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.</td></tr> |
| <tr><td><code>TIME_CEIL(timestamp_expr, period, [origin, [timezone]])</code></td><td>Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify <code>origin</code> as a timestamp to set the reference time for rounding. For example, <code>TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')</code> measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See <a href="/docs/latest/querying/granularities.html">Period granularities</a> for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to <code>CEIL</code> but is more flexible.</td></tr> |
| <tr><td><code>TIME_FLOOR(timestamp_expr, period, [origin, [timezone]])</code></td><td>Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify <code>origin</code> as a timestamp to set the reference time for rounding. For example, <code>TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')</code> measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See <a href="/docs/latest/querying/granularities.html">Period granularities</a> for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to <code>FLOOR</code> but is more flexible.</td></tr> |
| <tr><td><code>TIME_SHIFT(timestamp_expr, period, step, [timezone])</code></td><td>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><code>TIME_EXTRACT(timestamp_expr, [unit, [timezone]])</code></td><td>Extracts a time part from <code>expr</code>, returning it as a number. Unit can be EPOCH, 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". This function is similar to <code>EXTRACT</code> but is more flexible. Unit and time zone must be literals, and must be provided quoted, like <code>TIME_EXTRACT(__time, 'HOUR')</code> or <code>TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')</code>.</td></tr> |
| <tr><td><code>TIME_PARSE(string_expr, [pattern, [timezone]])</code></td><td>Parses a string into a timestamp using a given <a href="http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html">Joda DateTimeFormat pattern</a>, or ISO8601 (e.g. <code>2000-01-02T03:04:05Z</code>) 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", 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 NULL.</td></tr> |
| <tr><td><code>TIME_FORMAT(timestamp_expr, [pattern, [timezone]])</code></td><td>Formats a timestamp as a string with a given <a href="http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html">Joda DateTimeFormat pattern</a>, or ISO8601 (e.g. <code>2000-01-02T03:04:05Z</code>) 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> |
| <tr><td><code>TIME_IN_INTERVAL(timestamp_expr, interval)</code></td><td>Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as <code>'2001-01-01/P1D'</code> or <code>'2001-01-01T01:00:00/2001-01-02T01:00:00'</code>. The start instant of the interval is inclusive and the end instant is exclusive.</td></tr> |
| <tr><td><code>MILLIS_TO_TIMESTAMP(millis_expr)</code></td><td>Converts a number of milliseconds since the epoch (1970-01-01 00:00:00 UTC) into a timestamp.</td></tr> |
| <tr><td><code>TIMESTAMP_TO_MILLIS(timestamp_expr)</code></td><td>Converts a timestamp into a number of milliseconds since the epoch.</td></tr> |
| <tr><td><code>EXTRACT(unit FROM timestamp_expr)</code></td><td>Extracts a time part from <code>expr</code>, returning it as a number. Unit can be EPOCH, MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), ISODOW (ISO day of week), DOY (day of year), WEEK (week of year), MONTH, QUARTER, YEAR, ISOYEAR, DECADE, CENTURY or MILLENNIUM. Units must be provided unquoted, like <code>EXTRACT(HOUR FROM __time)</code>.</td></tr> |
| <tr><td><code>FLOOR(timestamp_expr TO unit)</code></td><td>Rounds down a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.</td></tr> |
| <tr><td><code>CEIL(timestamp_expr TO unit)</code></td><td>Rounds up a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.</td></tr> |
| <tr><td><code>TIMESTAMPADD(unit, count, timestamp)</code></td><td>Equivalent to <code>timestamp + count * INTERVAL '1' UNIT</code>.</td></tr> |
| <tr><td><code>TIMESTAMPDIFF(unit, timestamp1, timestamp2)</code></td><td>Returns the (signed) number of <code>unit</code> between <code>timestamp1</code> and <code>timestamp2</code>. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.</td></tr> |
| </tbody> |
| </table> |
| <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 argument 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>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>GREATEST([expr1, ...])</code></td><td>Evaluates zero or more expressions and returns the maximum value based on comparisons as described above.</td></tr> |
| <tr><td><code>LEAST([expr1, ...])</code></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 can either be an IPv4 dotted-decimal string |
| (e.g., "192.168.0.1") or an IP address represented as an integer (e.g., 3232235521). The <code>subnet</code> |
| argument should be a string formatted as an IPv4 address subnet in CIDR notation (e.g., "192.168.0.0/16").</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>IPV4_MATCH(address, subnet)</code></td><td>Returns true if the <code>address</code> belongs to the <code>subnet</code> literal, else false. If <code>address</code> is not a valid IPv4 address, then false is returned. This function is more efficient if <code>address</code> is an integer instead of a string.</td></tr> |
| <tr><td><code>IPV4_PARSE(address)</code></td><td>Parses <code>address</code> into an IPv4 address stored as an integer . If <code>address</code> is an integer that is a valid IPv4 address, then it is passed through. Returns null if <code>address</code> cannot be represented as an IPv4 address.</td></tr> |
| <tr><td><code>IPV4_STRINGIFY(address)</code></td><td>Converts <code>address</code> into an IPv4 address dotted-decimal string. If <code>address</code> is a string that is a valid IPv4 address, then it is passed through. 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="sketch-functions"></a><a href="#sketch-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>Sketch functions</h2> |
| <p>These functions operate on expressions or columns that return sketch objects. |
| To create sketch objects, see the <a href="/docs/latest/querying/sql-aggregations.html#sketch-functions">DataSketches aggregators</a>.</p> |
| <h3><a class="anchor" aria-hidden="true" id="hll-sketch-functions"></a><a href="#hll-sketch-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>HLL sketch functions</h3> |
| <p>The following functions operate on <a href="/docs/latest/development/extensions-core/datasketches-hll.html">DataSketches HLL sketches</a>. |
| The <a href="/docs/latest/development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>HLL_SKETCH_ESTIMATE(expr, [round])</code></td><td>Returns the distinct count estimate from an HLL sketch. <code>expr</code> must return an HLL sketch. The optional <code>round</code> boolean parameter will round the estimate if set to <code>true</code>, with a default of <code>false</code>.</td></tr> |
| <tr><td><code>HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, [numStdDev])</code></td><td>Returns the distinct count estimate and error bounds from an HLL sketch. <code>expr</code> must return an HLL sketch. An optional <code>numStdDev</code> argument can be provided.</td></tr> |
| <tr><td><code>HLL_SKETCH_UNION([lgK, tgtHllType], expr0, expr1, ...)</code></td><td>Returns a union of HLL sketches, where each input expression must return an HLL sketch. The <code>lgK</code> and <code>tgtHllType</code> can be optionally specified as the first parameter; if provided, both optional parameters must be specified.</td></tr> |
| <tr><td><code>HLL_SKETCH_TO_STRING(expr)</code></td><td>Returns a human-readable string representation of an HLL sketch for debugging. <code>expr</code> must return an HLL sketch.</td></tr> |
| </tbody> |
| </table> |
| <h3><a class="anchor" aria-hidden="true" id="theta-sketch-functions"></a><a href="#theta-sketch-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>Theta sketch functions</h3> |
| <p>The following functions operate on <a href="/docs/latest/development/extensions-core/datasketches-theta.html">theta sketches</a>. |
| The <a href="/docs/latest/development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>THETA_SKETCH_ESTIMATE(expr)</code></td><td>Returns the distinct count estimate from a theta sketch. <code>expr</code> must return a theta sketch.</td></tr> |
| <tr><td><code>THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, errorBoundsStdDev)</code></td><td>Returns the distinct count estimate and error bounds from a theta sketch. <code>expr</code> must return a theta sketch.</td></tr> |
| <tr><td><code>THETA_SKETCH_UNION([size], expr0, expr1, ...)</code></td><td>Returns a union of theta sketches, where each input expression must return a theta sketch. The <code>size</code> can be optionally specified as the first parameter.</td></tr> |
| <tr><td><code>THETA_SKETCH_INTERSECT([size], expr0, expr1, ...)</code></td><td>Returns an intersection of theta sketches, where each input expression must return a theta sketch. The <code>size</code> can be optionally specified as the first parameter.</td></tr> |
| <tr><td><code>THETA_SKETCH_NOT([size], expr0, expr1, ...)</code></td><td>Returns a set difference of theta sketches, where each input expression must return a theta sketch. The <code>size</code> can be optionally specified as the first parameter.</td></tr> |
| </tbody> |
| </table> |
| <h3><a class="anchor" aria-hidden="true" id="quantiles-sketch-functions"></a><a href="#quantiles-sketch-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>Quantiles sketch functions</h3> |
| <p>The following functions operate on <a href="/docs/latest/development/extensions-core/datasketches-quantiles.html">quantiles sketches</a>. |
| The <a href="/docs/latest/development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>DS_GET_QUANTILE(expr, fraction)</code></td><td>Returns the quantile estimate corresponding to <code>fraction</code> from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr> |
| <tr><td><code>DS_GET_QUANTILES(expr, fraction0, fraction1, ...)</code></td><td>Returns a string representing an array of quantile estimates corresponding to a list of fractions from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr> |
| <tr><td><code>DS_HISTOGRAM(expr, splitPoint0, splitPoint1, ...)</code></td><td>Returns a string representing an approximation to the histogram given a list of split points that define the histogram bins from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr> |
| <tr><td><code>DS_CDF(expr, splitPoint0, splitPoint1, ...)</code></td><td>Returns a string representing approximation to the Cumulative Distribution Function given a list of split points that define the edges of the bins from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr> |
| <tr><td><code>DS_RANK(expr, value)</code></td><td>Returns an approximation to the rank of a given value that is the fraction of the distribution less than that value from a quantiles sketch. <code>expr</code> must return a quantiles sketch.</td></tr> |
| <tr><td><code>DS_QUANTILE_SUMMARY(expr)</code></td><td>Returns a string summary of a quantiles sketch, useful for debugging. <code>expr</code> must return a quantiles sketch.</td></tr> |
| </tbody> |
| </table> |
| <h3><a class="anchor" aria-hidden="true" id="tuple-sketch-functions"></a><a href="#tuple-sketch-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>Tuple sketch functions</h3> |
| <p>The following functions operate on <a href="/docs/latest/development/extensions-core/datasketches-tuple.html">tuple sketches</a>. |
| The <a href="/docs/latest/development/extensions-core/datasketches-extension.html">DataSketches extension</a> must be loaded to use the following functions.</p> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th><th>Default</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(expr)</code></td><td>Computes approximate sums of the values contained within a <a href="/docs/latest/development/extensions-core/datasketches-tuple.html#estimated-metrics-values-for-each-column-of-arrayofdoublessketch">Tuple sketch</a> column which contains an array of double values as its Summary Object.</td></tr> |
| <tr><td><code>DS_TUPLE_DOUBLES_INTERSECT(expr, ..., [nominalEntries])</code></td><td>Returns an intersection of tuple sketches, where each input expression must return a tuple sketch which contains an array of double values as its Summary Object. The values contained in the Summary Objects are summed when combined. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for <a href="/docs/latest/development/extensions-core/datasketches-tuple.html">nominal entries</a>.</td></tr> |
| <tr><td><code>DS_TUPLE_DOUBLES_NOT(expr, ..., [nominalEntries])</code></td><td>Returns a set difference of tuple sketches, where each input expression must return a tuple sketch which contains an array of double values as its Summary Object. The values contained in the Summary Object are preserved as is. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for <a href="/docs/latest/development/extensions-core/datasketches-tuple.html">nominal entries</a>.</td></tr> |
| <tr><td><code>DS_TUPLE_DOUBLES_UNION(expr, ..., [nominalEntries])</code></td><td>Returns a union of tuple sketches, where each input expression must return a tuple sketch which contains an array of double values as its Summary Object. The values contained in the Summary Objects are summed when combined. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for <a href="/docs/latest/development/extensions-core/datasketches-tuple.html">nominal entries</a>.</td></tr> |
| </tbody> |
| </table> |
| <h2><a class="anchor" aria-hidden="true" id="other-scalar-functions"></a><a href="#other-scalar-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 scalar functions</h2> |
| <table> |
| <thead> |
| <tr><th>Function</th><th>Notes</th></tr> |
| </thead> |
| <tbody> |
| <tr><td><code>CAST(value AS TYPE)</code></td><td>Cast value to another type. See <a href="/docs/latest/querying/sql-data-types.html">Data types</a> for details about how Druid SQL handles CAST.</td></tr> |
| <tr><td><code>CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[ ELSE resultN \] END</code></td><td>Simple CASE.</td></tr> |
| <tr><td><code>CASE WHEN boolean_expr1 THEN result1 \[ WHEN boolean_expr2 THEN result2 ... \] \[ ELSE resultN \] END</code></td><td>Searched CASE.</td></tr> |
| <tr><td><code>NULLIF(value1, value2)</code></td><td>Returns NULL if value1 and value2 match, else returns value1.</td></tr> |
| <tr><td><code>COALESCE(value1, value2, ...)</code></td><td>Returns the first value that is neither NULL nor empty string.</td></tr> |
| <tr><td><code>NVL(expr, expr-for-null)</code></td><td>Returns <code>expr-for-null</code> if <code>expr</code> is null (or empty string for string type).</td></tr> |
| <tr><td><code>BLOOM_FILTER_TEST(expr, serialized-filter)</code></td><td>Returns true if the value of <code>expr</code> is contained in the Base64-serialized Bloom filter. See the <a href="/docs/latest/development/extensions-core/bloom-filter.html">Bloom filter extension</a> documentation for additional details. See the <a href="/docs/latest/querying/sql-aggregations.html"><code>BLOOM_FILTER</code> function</a> for computing Bloom filters.</td></tr> |
| </tbody> |
| </table> |
| </span></div></article></div><div class="docs-prevnext"><a class="docs-prev button" href="/docs/latest/querying/sql-operators.html"><span class="arrow-prev">← </span><span>Operators</span></a><a class="docs-next button" href="/docs/latest/querying/sql-aggregations.html"><span>Aggregation functions</span><span class="arrow-next"> →</span></a></div></div></div><nav class="onPageNav"><ul class="toc-headings"><li><a href="#numeric-functions">Numeric functions</a></li><li><a href="#string-functions">String functions</a></li><li><a href="#date-and-time-functions">Date and time functions</a></li><li><a href="#reduction-functions">Reduction functions</a></li><li><a href="#ip-address-functions">IP address functions</a></li><li><a href="#sketch-functions">Sketch functions</a><ul class="toc-headings"><li><a href="#hll-sketch-functions">HLL sketch functions</a></li><li><a href="#theta-sketch-functions">Theta sketch functions</a></li><li><a href="#quantiles-sketch-functions">Quantiles sketch functions</a></li><li><a href="#tuple-sketch-functions">Tuple sketch functions</a></li></ul></li><li><a href="#other-scalar-functions">Other scalar functions</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/">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 © 2022 <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/npm/docsearch.js@2/dist/cdn/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({ |
| appId: 'CPK9PMSCEY', |
| apiKey: 'd4ef4ffe3a2f0c7d1e34b062fd98736b', |
| indexName: 'apache_druid', |
| inputSelector: '#search_input_react', |
| algoliaOptions: {"facetFilters":["language:en","version:26.0.0"]} |
| }); |
| </script></body></html> |
