Added: kylin/site/docs/tutorial/sql_reference.html URL: http://svn.apache.org/viewvc/kylin/site/docs/tutorial/sql_reference.html?rev=1852525&view=auto ============================================================================== --- kylin/site/docs/tutorial/sql_reference.html (added) +++ kylin/site/docs/tutorial/sql_reference.html Wed Jan 30 14:13:48 2019 @@ -0,0 +1,12789 @@ +<!-- +* 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. +--> + +<!doctype html> +<html> + <!-- +* 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. +--> + +<head> + <meta charset="utf-8"> + <meta http-equiv="X-UA-Compatible" content="IE=edge"> + <meta name="viewport" content="width=device-width, initial-scale=1"> + + <title>Apache Kylin | SQL Reference</title> + <meta name="description" content="Apache Kylin Home"> + <meta name="author" content="Apache Kylin"> + <link rel="shortcut icon" href="fav.png" type="image/png"> + + + +<link rel="stylesheet" href="/assets/css/animate.css"> +<!-- Bootstrap --> +<link rel="stylesheet" href="/assets/css/bootstrap.min.css"> + +<!-- Fonts --> +<!-- <link rel="stylesheet" href="http://fonts.googleapis.com/css?family=Alice|Open+Sans:400,300,700"> --> + +<!-- Icons --> +<link rel="stylesheet" href="/assets/css/font-awesome.min.css"> + + <!-- Custom styles --> + <link rel="stylesheet" href="/assets/css/styles.css"> + <link rel="stylesheet" href="/assets/css/docs.css"> + <link rel="stylesheet" href="/assets/css/pygments.css"> + + <link rel="canonical" href="http://kylin.apache.org/docs/tutorial/sql_reference.html";> + <link rel="alternate" type="application/rss+xml" title="Apache Kylin" href="http://kylin.apache.org/feed.xml"; /> + +<!--[if lt IE 9]> <script src="assets/js/html5shiv.js"></script> <![endif]--> +<!-- Global site tag (gtag.js) - Google Analytics --> +<script async src="https://www.googletagmanager.com/gtag/js?id=UA-120788561-1";></script> +<script> + window.dataLayer = window.dataLayer || []; + function gtag(){dataLayer.push(arguments);} + gtag('js', new Date()); + + gtag('config', 'UA-120788561-1'); +</script> +<script type="text/javascript" src="/assets/js/jquery-1.9.1.min.js"></script> +<script type="text/javascript" src="/assets/js/nside.js"></script> </script> +<script type="text/javascript" src="/assets/js/nnav.js"></script> </script> +<script> +var _hmt = _hmt || []; +(function() { + var hm = document.createElement("script"); + hm.src = "https://hm.baidu.com/hm.js?bdc5e03add430c0b72cc0eb91eabfa99";; + var s = document.getElementsByTagName("script")[0]; + s.parentNode.insertBefore(hm, s); +})(); +</script> + +</head> + + <body> + <!-- +* 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. +--> + +<header id="header" > + + <!-- Main Menu --> + <nav class="navbar navbar-default" role="navigation" id="nav-wrapper"> + <div class="container-fluid" id="nav"> + <!-- + <img class="img-circle" width="40px" height="40px" id="circlelogo" src="/assets/images/kylin_logo.jpg"> + --> + <!-- Brand and toggle get grouped for better mobile display --> + <div class="navbar-header"> + <img class="navbar-logo" width="46" src="/assets/images/kylin_logo.png" ></img> + <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1"> + <span class="sr-only">Toggle navigation</span> + <span class="icon-bar"></span> + <span class="icon-bar"></span> + <span class="icon-bar"></span> + </button> + <ul class="nav icon-navbar"> + <li><a href="https://twitter.com/apachekylin"; target="_blank" class="fa fa-twitter fa-lg" title="Twitter: @ApacheKylin" ></a></li> + <li><a href="https://github.com/apache/kylin"; target="_blank" class="fa fa-github-alt fa-lg" title="Github: apache/kylin" ></a></li> + <li><a href="https://www.facebook.com/kylinio"; target="_blank" class="fa fa-facebook fa-lg" title="Facebook: kylin.io" ></a></li> + </ul> + </div> + + <!-- Collect the nav links, forms, and other content for toggling --> + <div class="navbar-collapse collapse" id="bs-example-navbar-collapse-1"> + + <ul class="nav navbar-nav"> + + <li><a href="/">Home</a></li> + <li> + <a href="/docs">Docs</a> +<!-- + <a href="#" class="dropdown-toggle" data-toggle="dropdown">Docs</a> + <ul class="dropdown-menu"> + + <li class="dropdown-submenu"><a href="#titleGetting Started" data-toggle="collapse" class="navtitle">Getting Started</a> + <ul class="dropdown-menu"> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/index.html" class="list-group-item-lay pjaxlink">Overview</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/release_notes.html" class="list-group-item-lay pjaxlink">Release Notes</a> + </li> + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/concepts.html" class="list-group-item-lay pjaxlink">Technical Concepts</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/terminology.html" class="list-group-item-lay pjaxlink">Terminology</a> + </li> + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/faq.html" class="list-group-item-lay pjaxlink">FAQ</a> + </li> + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/events.html" class="list-group-item-lay pjaxlink">Events and Conferences</a> + </li> + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/best_practices.html" class="list-group-item-lay pjaxlink">Community Best Practices</a> + </li> + + + + + + + + + <ul> + </li> + + <li class="dropdown-submenu"><a href="#titleInstallation" data-toggle="collapse" class="navtitle">Installation</a> + <ul class="dropdown-menu"> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/index.html" class="list-group-item-lay pjaxlink">Installation Guide</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/kylin_cluster.html" class="list-group-item-lay pjaxlink">Deploy in Cluster Mode</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/configuration.html" class="list-group-item-lay pjaxlink">Kylin Configuration</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/kylin_aws_emr.html" class="list-group-item-lay pjaxlink">Install Kylin on AWS EMR</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/kylin_docker.html" class="list-group-item-lay pjaxlink">Run Kylin with Docker</a> + </li> + + + + + + + + + <ul> + </li> + + <li class="dropdown-submenu"><a href="#titleTutorial" data-toggle="collapse" class="navtitle">Tutorial</a> + <ul class="dropdown-menu"> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/kylin_sample.html" class="list-group-item-lay pjaxlink">Quick Start with Sample Cube</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/web.html" class="list-group-item-lay pjaxlink">Web Interface</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/create_cube.html" class="list-group-item-lay pjaxlink">Cube Wizard</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_build_job.html" class="list-group-item-lay pjaxlink">Cube Build and Job Monitoring</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/sql_reference.html" class="list-group-item-lay pjaxlink">SQL Reference</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/project_level_acl.html" class="list-group-item-lay pjaxlink">Project Level ACL</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_spark.html" class="list-group-item-lay pjaxlink">Build Cube with Spark</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_streaming.html" class="list-group-item-lay pjaxlink">Scalable Cubing from Kafka</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_build_performance.html" class="list-group-item-lay pjaxlink">Cube Build Tuning</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/kylin_client_tool.html" class="list-group-item-lay pjaxlink">Kylin Python Client</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/setup_systemcube.html" class="list-group-item-lay pjaxlink">Set Up System Cube</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/use_cube_planner.html" class="list-group-item-lay pjaxlink">Use Cube Planner</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/use_dashboard.html" class="list-group-item-lay pjaxlink">Use Dashboard</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/setup_jdbc_datasource.html" class="list-group-item-lay pjaxlink">Setup JDBC Data Source</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/hybrid.html" class="list-group-item-lay pjaxlink">Hybrid Model</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/mysql_metastore.html" class="list-group-item-lay pjaxlink">Use MySQL as Metastore</a> + </li> + + + + + + + + + <ul> + </li> + + <li class="dropdown-submenu"><a href="#titleIntegration" data-toggle="collapse" class="navtitle">Integration</a> + <ul class="dropdown-menu"> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/odbc.html" class="list-group-item-lay pjaxlink">Kylin ODBC Driver</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/jdbc.html" class="list-group-item-lay pjaxlink">Kylin JDBC Driver</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/tableau.html" class="list-group-item-lay pjaxlink">Tableau 8</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/tableau_91.html" class="list-group-item-lay pjaxlink">Tableau 9</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/powerbi.html" class="list-group-item-lay pjaxlink">MS Excel and Power BI</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/microstrategy.html" class="list-group-item-lay pjaxlink">MicroStrategy</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/squirrel.html" class="list-group-item-lay pjaxlink">SQuirreL</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/flink.html" class="list-group-item-lay pjaxlink">Apache Flink</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/spark.html" class="list-group-item-lay pjaxlink">Apache Spark</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/hue.html" class="list-group-item-lay pjaxlink">Hue</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/Qlik.html" class="list-group-item-lay pjaxlink">Qlik Sense</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/superset.html" class="list-group-item-lay pjaxlink">Superset</a> + </li> + + + + + + + + + <ul> + </li> + + <li class="dropdown-submenu"><a href="#titleHow To" data-toggle="collapse" class="navtitle">How To</a> + <ul class="dropdown-menu"> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_use_restapi.html" class="list-group-item-lay pjaxlink">Use RESTful API</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_build_cube_with_restapi.html" class="list-group-item-lay pjaxlink">Build Cube with API</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_optimize_cubes.html" class="list-group-item-lay pjaxlink">Optimize Cube Design</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_optimize_build.html" class="list-group-item-lay pjaxlink">Optimize Cube Build</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_backup_metadata.html" class="list-group-item-lay pjaxlink">Backup Metadata</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_cleanup_storage.html" class="list-group-item-lay pjaxlink">Cleanup Storage</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_upgrade.html" class="list-group-item-lay pjaxlink">Upgrade From Old Versions</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_use_cli.html" class="list-group-item-lay pjaxlink">Use Utility CLIs</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_ldap_and_sso.html" class="list-group-item-lay pjaxlink">Secure with LDAP and SSO</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_use_beeline.html" class="list-group-item-lay pjaxlink">Use Beeline for Hive</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_update_coprocessor.html" class="list-group-item-lay pjaxlink">Update Coprocessor</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_install_ranger_kylin_plugin.html" class="list-group-item-lay pjaxlink">Install Ranger Plugin</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/howto/howto_enable_zookeeper_acl.html" class="list-group-item-lay pjaxlink">Enable Zookeeper ACL</a> + </li> + + + + + + + + + <ul> + </li> + + </ul> --> + </li> + <li><a href="/download">Download</a></li> + <li><a href="/community" >Community</a></li> + <li><a href="/development" >Development</a></li> + <li><a href="/blog">Blog</a></li> + <li><a href="/cn" >ä¸æç</a></li> + </ul> + </div><!-- /.navbar-collapse --> + </div><!-- /.container-fluid --> + </nav> + + <div id="head" class="parallax normal-header" > + <div class="text-center header-apache"> + <a href="http://apache.org/foundation/contributing.html"; title="Support Apache" style="margin-left: 150px;"> + <div> + <img src="https://www.apache.org/images/SupportApache-small.png"; > + </div> + </a> + </div> + </div> + + </header> + + + <div class="container"> + <div class="row" style="clear:both;"> + <div class="col-md-8 col-lg-8 col-xs-12 col-sm-12 aside2" style="float:right;"> + <div id="container" class="docs"> + <div id="pjax"> + <h1 class="post-title">SQL Reference</h1> + + + <article class="post-content" > + <p>Kylin uses Calcite for the query. In principle, Kylin supports all statements that Calcite recognizes.</p> + +<h2 id="grammar">Grammar</h2> + +<p><a href="#QUERYSYNTAX">QUERY SYNTAX</a><br /> +<a href="#SELECT">SELECT</a><br /> +ã<a href="#STATEMENT">STATEMENT</a><br /> +ã<a href="#EXPRESSION">EXPRESSION</a><br /> +<a href="#SUBQUERY">SUBQUERY</a><br /> +<a href="#JOIN">JOIN</a><br /> +ã<a href="#INNERJOIN">INNER JOIN</a><br /> +ã<a href="#LEFTJOIN">LEFT JOIN</a><br /> +<a href="#UNION">UNION</a><br /> +<a href="#UNIONALL">UNION ALL</a></p> + +<h2 id="function">Function</h2> + +<p><a href="#COUNT">COUNT</a><br /> +ã<a href="#COUNT_COLUMN">COUNT(COLUMN)</a><br /> +ã<a href="#COUNT_">COUNT(*)</a><br /> +<a href="#COUNT_DISTINCT">COUNT_DISTINCT</a><br /> +<a href="#MAX">MAX</a><br /> +<a href="#MIN">MIN</a><br /> +<a href="#PERCENTILE">PERCENTILE</a><br /> +<a href="#SUM">SUM</a><br /> +<a href="#TOP_N">TOP_N</a><br /> +<a href="#WINDOW">WINDOW</a><br /> +ã<a href="#ROW_NUMBER">ROW_NUMBER</a><br /> +ã<a href="#AVG">AVG</a><br /> +ã<a href="#RANK">RANK</a><br /> +ã<a href="#DENSE_RANK">DENSE_RANK</a><br /> +ã<a href="#FIRST_VALUE">FIRST_VALUE</a><br /> +ã<a href="#LAST_VALUE">LAST_VALUE</a><br /> +ã<a href="#LAG">LAG</a><br /> +ã<a href="#LEAD">LEAD</a><br /> +ã<a href="#NTILE">NTILE</a><br /> +ã<a href="#CASEWHEN">CASE WHEN</a><br /> +ã<a href="#CAST">CAST</a><br /> +<a href="#SUBSTRING">SUSTRING</a><br /> +<a href="#COALESCE">COALESCE</a></p> + +<h2 id="data-type">Data Type</h2> + +<p><a href="#DATATYPE">DATA TYPE</a></p> + +<h2 id="QUERYSYNTAX">QUERY SYNTAX</h2> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">statement: +| query + +query: + values + | WITH withItem [ , withItem ]* query + | { + select + | selectWithoutFrom + | query UNION [ ALL | DISTINCT ] query + | query INTERSECT [ ALL | DISTINCT ] query + } + [ ORDER BY orderItem [, orderItem ]* ] + [ LIMIT { count | ALL } ] + [ OFFSET start { ROW | ROWS } ] + [ FETCH { FIRST | NEXT } [ count ] { ROW| ROWS } ] + +withItem: + name + ['(' column [, column ]* ')' ] + AS '(' query ')' + +orderItem: + expression [ ASC | DESC ]ï¼» NULLS FIRST |NULLS LAST ï¼½ + +select: + SELECT [ ALL | DISTINCT] + { * | projectItem [, projectItem ]* } + FROM tableExpression + [ WHERE booleanExpression ] + [ GROUP BY { groupItem [, groupItem ]* }] + [ HAVING booleanExpression ] + [ WINDOW windowName AS windowSpec [,windowName AS windowSpec ]* ] + +selectWithoutFrom: + SELECT [ ALL | DISTINCT ] + { * | projectItem [, projectItem ]* } + +projectItem: + expression [ [ AS ] columnAlias ] + | tableAlias . * + +tableExpression: + tableReference [, tableReference ]* + | tableExpression [ NATURAL ]ï¼»( LEFT | RIGHT | FULL ) [ OUTER ] ï¼½ JOIN tableExpression [ joinCondition ] + +joinCondition: + ON booleanExpression + | USING '(' column [, column ]* ')' + +tableReference: + tablePrimary + [ matchRecognize ] + [ [ AS ] alias [ '(' columnAlias [,columnAlias ]* ')' ] ] + +tablePrimary: + [ [ catalogName . ] schemaName . ] tableName + '(' TABLE [ [ catalogName . ] schemaName. ] tableName ')' + | [ LATERAL ] '(' query ')' + | UNNEST '(' expression ')' [ WITH ORDINALITY ] + | [ LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]*')' ')' + +values: + VALUES expression [, expression ]* + +groupItem: + expression + | '('')' + | '('expression [, expression ]* ')' + | GROUPING SETS '(' groupItem [, groupItem ]* ')' + +windowRef: + windowName + | windowSpec + +windowSpec: + [windowName ] + '(' + [ ORDER BY orderItem [, orderItem ]* ] + [ PARTITION BY expression [, expression]* ] + [ + RANGE numericOrIntervalExpression { PRECEDING | FOLLOWING } + | ROWS numericExpression { PRECEDING | FOLLOWING } + ] + ')'</code></pre></div> + +<h2 id="SELECT">SELECT</h2> + +<h3 id="STATEMENT">STATEMENT</h3> +<p><code class="highlighter-rouge">SELECT</code> chooses the data from the table. <code class="highlighter-rouge">COUNT</code> is used for quantitative statistics. <code class="highlighter-rouge">DISTINCT</code> filters out duplicate results. <code class="highlighter-rouge">AS</code> is used to alias tables or columns. <code class="highlighter-rouge">FROM</code> identifies the table being queried. <code class="highlighter-rouge">JOIN</code> is used to connect two tables to get the desired data. <code class="highlighter-rouge">WHERE</code> is used to specify the standard of selection. <code class="highlighter-rouge">LIKE</code> is used to search for a specified pattern in a column in a <code class="highlighter-rouge">WHERE</code> clause. <code class="highlighter-rouge">BETWEEN ... AND</code> is used to select a range of data between two values. <code class="highlighter-rouge">AND</code> and <code class="highlighter-rouge">OR</code> is used to filter records based on more than one con dition. <code class="highlighter-rouge">GROUP BY</code> groups the result by the given expression(s). <code class="highlighter-rouge">HAVING</code> filters rows after grouping. <code class="highlighter-rouge">ORDER BY</code> sorts the result based on the given expression, usually uses with <code class="highlighter-rouge">TOPN</code> function. <code class="highlighter-rouge">LIMIT</code> limits the number of rows returned by the query.</p> + +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT COUNT(*) FROM kylin_sales; + +SELECT COUNT(DISTINCT seller_id) FROM kylin_sales; + +SELECT seller_id, COUNT(1) FROM kylin_sales GROUP BY seller_id; + +SELECT lstg_format_name, SUM(price) AS gmv, COUNT(DISTINCT seller_id) AS dist_seller FROM kylin_sales WHERE lstg_format_name='FP-GTC' GROUP BY lstg_format_name HAVING COUNT(DISTINCT seller_id) > 50; + +SELECT lstg_format_name FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt WHERE NOT(lstg_format_name NOT LIKE '%ab%') GROUP BY lstg_format_name; + +SELECT kylin_cal_dt.cal_dt FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt WHERE kylin_cal_dt.cal_dt BETWEEN DATE '2013-01-01' AND DATE '2013-06-04'; + +SELECT kylin_sales.lstg_format_name, SUM(kylin_sales.price) AS gmv, COUNT(*) AS trans_cnt FROM kylin_sales WHERE kylin_sales.lstg_format_name IS NULL GROUP BY kylin_sales.lstg_format_name HAVING SUM(price)>5000 AND COUNT(*)>72; + +SELECT kylin_sales.lstg_format_name, SUM(kylin_sales.price) AS gmv, COUNT(*) AS trans_cnt FROM kylin_sales WHERE kylin_sales.lstg_format_name IS NOT NULL GROUP BY kylin_sales.lstg_format_name HAVING SUM(price)>5000 OR COUNT(*)>20; + +SELECT lstg_format_name, SUM(price) AS gmv, COUNT(1) AS trans_cnt FROM kylin_sales WHERE lstg_format_name='FP-GTC' GROUP BY lstg_format_name ORDER BY lstg_format_name LIMIT 10;</code></pre></div> + +<h3 id="EXPRESSION">EXPRESSION</h3> +<p>An expression in a <code class="highlighter-rouge">SELECT</code> statement. All columns in a table may be selected using *.<br /> +Example:<br /> +1. *<br /> +2. ID AS VALUE<br /> +3. VALUE + 1</p> + +<h2 id="SUBQUERY">SUBQUERY</h2> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT cal_dt ,sum(price) AS sum_price FROM (SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt INNER JOIN kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id) t GROUP BY cal_dt;</code></pre></div> + +<h2 id="JOIN">JOIN</h2> + +<h3 id="INNERJOIN">INNER JOIN</h3> +<p>The <code class="highlighter-rouge">INNER JOIN</code> keyword returns rows when there is at least one match in the table.</p> + +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT kylin_cal_dt.cal_dt, kylin_sales.price FROM kylin_sales INNER JOIN kylin_cal_dt AS kylin_cal_dt ON kylin_sales.part_dt = kylin_cal_dt.cal_dt;</code></pre></div> + +<h3 id="LEFTJOIN">LEFT JOIN</h3> +<p>The <code class="highlighter-rouge">LEFT JOIN</code> keyword returns all rows from the left table (kylin_sales), even if there are no matching rows in the right table (kylin_category_groupings).</p> + +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT seller_id FROM kylin_sales LEFT JOIN kylin_category_groupings AS kylin_category_groupings ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id WHERE lstg_format_name='FP-GTC' GROUP BY seller_id LIMIT 20;</code></pre></div> + +<h2 id="UNION">UNION</h2> +<p>The <code class="highlighter-rouge">UNION</code> operator is used to combine the result sets of two or more <code class="highlighter-rouge">SELECT</code> statements.<br /> +<em>Note that</em> the <code class="highlighter-rouge">SELECT</code> statement inside <code class="highlighter-rouge">UNION</code> must have the same number of columns. Columns must also have similar data types. At the same time, the order of the columns in each <code class="highlighter-rouge">SELECT</code> statement must be the same.<br /> +By default, the <code class="highlighter-rouge">UNION</code> operator picks a different value. If you allow duplicate values, use <code class="highlighter-rouge">UNION ALL</code>.</p> + +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt < DATE '2012-08-01' GROUP BY seller_id UNION (SELECT SUM(price) AS x, seller_id, COUNT(*) AS y FROM kylin_sales WHERE part_dt > DATE '2012-12-01' GROUP BY seller_id);</code></pre></div> + +<h2 id="UNIONALL">UNION ALL</h2> +<p>The <code class="highlighter-rouge">UNION ALL</code> command is almost equivalent to the <code class="highlighter-rouge">UNION</code> command, but the <code class="highlighter-rouge">UNION ALL</code> command lists all values.</p> + +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_a WHERE trans_id <> 1 UNION ALL SELECT COUNT(trans_id) AS trans_id FROM kylin_sales AS test_b;</code></pre></div> + +<h2 id="COUNT">COUNT</h2> +<p>Returns the number of rows matching the specified criteria.</p> + +<h3 id="COUNT_COLUMN">COUNT(COLUMN)</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT COUNT(seller_id) FROM kylin_sales;</code></pre></div> + +<h3 id="COUNT_">COUNT(*)</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT COUNT(*) FROM kylin_sales;</code></pre></div> + +<h2 id="COUNT_DISTINCT">COUNT_DISTINCT</h2> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT COUNT(DISTINCT seller_id) AS DIST_SELLER FROM kylin_sales;</code></pre></div> + +<h2 id="MAX">MAX</h2> +<p>Returns the maximum value in a column. NULL values are not included in the calculation.<br /> +Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT MAX(lstg_site_id) FROM kylin_sales;</code></pre></div> + +<h2 id="MIN">MIN</h2> +<p>Returns the minimum value in a column. NULL values are not included in the calculation.<br /> +Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT MIN(lstg_site_id) FROM kylin_sales;</code></pre></div> + +<h2 id="PERCENTILE">PERCENTILE</h2> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT seller_id, PERCENTILE(price, 0.5) FROM kylin_sales GROUP BY seller_id; + +SELECT seller_id, PERCENTILE_APPROX(price, 0.5) FROM kylin_sales GROUP BY seller_id;</code></pre></div> + +<h2 id="SUM">SUM</h2> +<p>Returns the total number of numeric columns.<br /> +Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT SUM(price) FROM kylin_sales;</code></pre></div> + +<h2 id="TOP_N">TOP_N</h2> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT SUM(price) AS gmv + FROM kylin_sales +INNER JOIN kylin_cal_dt AS kylin_cal_dt + ON kylin_sales.part_dt = kylin_cal_dt.cal_dt + INNER JOIN kylin_category_groupings + ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id + WHERE kylin_cal_dt.cal_dt between DATE '2013-09-01' AND DATE '2013-10-01' AND (lstg_format_name='FP-GTC' OR 'a' = 'b') + GROUP BY kylin_cal_dt.cal_dt; + +SELECT kylin_sales.part_dt, seller_id +FROM kylin_sales +INNER JOIN kylin_cal_dt AS kylin_cal_dt +ON kylin_sales.part_dt = kylin_cal_dt.cal_dt +INNER JOIN kylin_category_groupings +ON kylin_sales.leaf_categ_id = kylin_category_groupings.leaf_categ_id +AND kylin_sales.lstg_site_id = kylin_category_groupings.site_id +GROUP BY +kylin_sales.part_dt, kylin_sales.seller_id ORDER BY SUM(kylin_sales.price) DESC LIMIT 20;</code></pre></div> + +<h2 id="WINDOW">WINDOW</h2> +<p>The <code class="highlighter-rouge">WINDOW</code> function performs the calculation on a set of table rows associated with the current row.<br /> +<em>NOTE</em>: <code class="highlighter-rouge">OVER</code> clause is necessary for window functions.</p> + +<h3 id="ROW_NUMBER">ROW_NUMBER</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT lstg_format_name, SUM(price) AS gmv, ROW_NUMBER() OVER() FROM kylin_sales GROUP BY lstg_format_name;</code></pre></div> + +<h3 id="AVG">AVG</h3> +<p>Returns the average of the numeric columns. NULL values are not included in the calculation.<br /> +Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT lstg_format_name, AVG(SUM(price)) OVER(PARTITION BY lstg_format_name) FROM kylin_sales GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="RANK">RANK</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price), RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="DENSE_RANK">DENSE_RANK</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price), DENSE_RANK() OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "dense_rank" FROM kylin_sales GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="FIRST_VALUE">FIRST_VALUE</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "first" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="LAST_VALUE">LAST_VALUE</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAST_VALUE(SUM(price)) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "current" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="LAG">LAG</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="LEAD">LEAD</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, LEAD(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) AS "next" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="NTILE">NTILE</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, NTILE(4) OVER (PARTITION BY lstg_format_name ORDER BY part_dt) AS "quarter" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="CASEWHEN">CASE WHEN</h3> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, (CASE LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) WHEN 0.0 THEN 0 ELSE SUM(price)/LAG(SUM(price), 1, 0.0) OVER(PARTITION BY lstg_format_name ORDER BY part_dt) END) AS "prev" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h3 id="CAST">CAST</h3> +<p>The keyword <code class="highlighter-rouge">RANGE</code>, <code class="highlighter-rouge">INTERVAL</code> to specify a range. <code class="highlighter-rouge">PRECEDING</code> means the first few days (second/minute/hour/month/year). <code class="highlighter-rouge">FOLLOWING</code> means the next few days (second/minute/hour/month/year).<br /> +Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT part_dt, lstg_format_name, SUM(price) AS gmv, FIRST_VALUE(SUM(price)) OVER (PARTITION BY lstg_format_name ORDER BY CAST(part_dt AS timestamp) RANGE INTERVAL '3' DAY PRECEDING) AS "prev 3 days", LAST_VALUE(SUM(price)) OVER (PARTITION BY lstg_format_name ORDER BY CAST(part_dt AS timestamp) RANGE INTERVAL '3' DAY FOLLOWING) AS "next 3 days" FROM kylin_sales WHERE part_dt < '2012-02-01' GROUP BY part_dt, lstg_format_name;</code></pre></div> + +<h2 id="SUBSTRING">SUBSTRING</h2> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT SUBSTRING(lstg_format_name, 1) FROM kylin_sales;</code></pre></div> + +<h2 id="COALESCE">COALESCE</h2> +<p>Example:</p> + +<div class="highlight"><pre><code class="language-groff" data-lang="groff">SELECT COALESCE(lstg_format_name, '888888888888') FROM kylin_sales;</code></pre></div> + +<h2 id="DATATYPE">DATA TYPE</h2> + +<table> + <tbody> + <tr> + <td>ANY</td> + <td>CHAR</td> + <td>VARCHAR</td> + <td>STRING</td> + <td>BOOLEAN</td> + </tr> + <tr> + <td>BYTE</td> + <td>BINARY</td> + <td>INT</td> + <td>SHORT</td> + <td>LONG</td> + </tr> + <tr> + <td>INTEGER</td> + <td>TINYINT</td> + <td>SMALLINT</td> + <td>BIGINT</td> + <td>TIMESTAMP</td> + </tr> + <tr> + <td>FLOAT</td> + <td>REAL</td> + <td>DOUBLE</td> + <td>DECIMAL</td> + <td>DATETIME</td> + </tr> + <tr> + <td>NUMERIC</td> + <td>DATE</td> + <td>TIME</td> + <td> </td> + <td> </td> + </tr> + </tbody> +</table> + + </article> + </div> + </div> + </div> + <!-- +* 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. +--> + +<div class="col-md-4 col-lg-4 col-xs-12 col-sm-12 aside1 visible-sm visible-xs visible-md visible-lg" id="nside1" style="margin-top: 5em;float:right;"> + <ul class="nav nav-pills nav-stacked"> + + <li><a href="#titleGetting Started" data-toggle="collapse" id="navtitle">Getting Started</a></li> + <div class="collapse in"> + <div class="list-group" id="list1"> + <ul> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/index.html" class="list-group-item-lay pjaxlink">Overview</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/release_notes.html" class="list-group-item-lay pjaxlink">Release Notes</a> + </li> + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/concepts.html" class="list-group-item-lay pjaxlink">Technical Concepts</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/terminology.html" class="list-group-item-lay pjaxlink">Terminology</a> + </li> + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/faq.html" class="list-group-item-lay pjaxlink">FAQ</a> + </li> + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/events.html" class="list-group-item-lay pjaxlink">Events and Conferences</a> + </li> + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/gettingstarted/best_practices.html" class="list-group-item-lay pjaxlink">Community Best Practices</a> + </li> + + + + + + + + + <ul> + </div> +</div> + + <li><a href="#titleInstallation" data-toggle="collapse" id="navtitle">Installation</a></li> + <div class="collapse in"> + <div class="list-group" id="list1"> + <ul> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/index.html" class="list-group-item-lay pjaxlink">Installation Guide</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/kylin_cluster.html" class="list-group-item-lay pjaxlink">Deploy in Cluster Mode</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/configuration.html" class="list-group-item-lay pjaxlink">Kylin Configuration</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/kylin_aws_emr.html" class="list-group-item-lay pjaxlink">Install Kylin on AWS EMR</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/install/kylin_docker.html" class="list-group-item-lay pjaxlink">Run Kylin with Docker</a> + </li> + + + + + + + + + <ul> + </div> +</div> + + <li><a href="#titleTutorial" data-toggle="collapse" id="navtitle">Tutorial</a></li> + <div class="collapse in"> + <div class="list-group" id="list1"> + <ul> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/kylin_sample.html" class="list-group-item-lay pjaxlink">Quick Start with Sample Cube</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/web.html" class="list-group-item-lay pjaxlink">Web Interface</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/create_cube.html" class="list-group-item-lay pjaxlink">Cube Wizard</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_build_job.html" class="list-group-item-lay pjaxlink">Cube Build and Job Monitoring</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/sql_reference.html" class="list-group-item-lay pjaxlink">SQL Reference</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/project_level_acl.html" class="list-group-item-lay pjaxlink">Project Level ACL</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_spark.html" class="list-group-item-lay pjaxlink">Build Cube with Spark</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_streaming.html" class="list-group-item-lay pjaxlink">Scalable Cubing from Kafka</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/cube_build_performance.html" class="list-group-item-lay pjaxlink">Cube Build Tuning</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/kylin_client_tool.html" class="list-group-item-lay pjaxlink">Kylin Python Client</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/setup_systemcube.html" class="list-group-item-lay pjaxlink">Set Up System Cube</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/use_cube_planner.html" class="list-group-item-lay pjaxlink">Use Cube Planner</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/use_dashboard.html" class="list-group-item-lay pjaxlink">Use Dashboard</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/setup_jdbc_datasource.html" class="list-group-item-lay pjaxlink">Setup JDBC Data Source</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/hybrid.html" class="list-group-item-lay pjaxlink">Hybrid Model</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/mysql_metastore.html" class="list-group-item-lay pjaxlink">Use MySQL as Metastore</a> + </li> + + + + + + + + + <ul> + </div> +</div> + + <li><a href="#titleIntegration" data-toggle="collapse" id="navtitle">Integration</a></li> + <div class="collapse in"> + <div class="list-group" id="list1"> + <ul> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/odbc.html" class="list-group-item-lay pjaxlink">Kylin ODBC Driver</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + <li class="navlist"> + <a href="/docs/tutorial/jdbc.html" class="list-group-item-lay pjaxlink">Kylin JDBC Driver</a> + </li> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
[... 2310 lines stripped ...]
![https://www.apache.org/images/SupportApache-small.png"](https://www.apache.org/images/SupportApache-small.png"){kind=link}