Home Database Mysql Tutorial hive中使用标准sql实现分组内排序

hive中使用标准sql实现分组内排序

Jun 07, 2016 pm 02:58 PM
hive sql use Group accomplish sort standard

hive中使用标准sql实现分组内排序 在hive中,想要实现分组内排序,一般都是自己写udf实现oracle中分析函数row_number() over(partition)的功能,如果不使用自定义udf,仅使用标准sql实现的话,毫无性能可言,仅做实验而已。 方便起见,以下语句为oracle中语句

hive中使用标准sql实现分组内排序

 

在hive中,想要实现分组内排序,一般都是自己写udf实现oracle中分析函数row_number() over(partition)的功能,如果不使用自定义udf,仅使用标准sql实现的话,毫无性能可言,仅做实验而已。

 

方便起见,以下语句为oracle中语句,但都属于标准sql,在hive中亦可:

 

Sql代码  

CREATE TABLE lxw_t (user_id VARCHAR2(20),  

class VARCHAR2(20),  

score NUMBER   

);  

 

Sql代码  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','语文','90');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','数学','86');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','英语','96');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_1','体育','77');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','语文','88');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','数学','65');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','英语','67');  

INSERT INTO liuxiaowen.lxw_t VALUES ('user_2','体育','98');  

  

commit;  

 

Sql代码  

SELECT x.user_id,x.class,x.score,COUNT(1) AS seq   

FROM (  

  SELECT a.*,b.score score2 FROM   

  liuxiaowen.lxw_t a ,  

  liuxiaowen.lxw_t b   

  WHERE a.user_id = b.user_id (+)   

) x   

WHERE x.score2 >= x.score --按照成绩由高到低排列  

--WHERE x.score >= x.score2  --按照成绩由低到高排列  

GROUP BY x.user_id,x.class,x.score   

order BY 1,4   

 

按照成绩由高到低排列的结果如下:

 

Sql代码  

USER_ID CLASS   SCORE   SEQ  

user_1  英语  96  1  

user_1  语文  90  2  

user_1  数学  86  3  

user_1  体育  77  4  

user_2  体育  98  1  

user_2  语文  88  2  

user_2  英语  67  3  

user_2  数学  65  4  

 

按照成绩由低到高排列的结果如下:

 

Sql代码  

USER_ID CLASS   SCORE   SEQ  

user_1  体育  77  1  

user_1  数学  86  2  

user_1  语文  90  3  

user_1  英语  96  4  

user_2  数学  65  1  

user_2  英语  67  2  

user_2  语文  88  3  

user_2  体育  98  4  

 

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1662
14
PHP Tutorial
1262
29
C# Tutorial
1235
24
What is the difference between HQL and SQL in Hibernate framework? What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

BTCC tutorial: How to bind and use MetaMask wallet on BTCC exchange? BTCC tutorial: How to bind and use MetaMask wallet on BTCC exchange? Apr 26, 2024 am 09:40 AM

MetaMask (also called Little Fox Wallet in Chinese) is a free and well-received encryption wallet software. Currently, BTCC supports binding to the MetaMask wallet. After binding, you can use the MetaMask wallet to quickly log in, store value, buy coins, etc., and you can also get 20 USDT trial bonus for the first time binding. In the BTCCMetaMask wallet tutorial, we will introduce in detail how to register and use MetaMask, and how to bind and use the Little Fox wallet in BTCC. What is MetaMask wallet? With over 30 million users, MetaMask Little Fox Wallet is one of the most popular cryptocurrency wallets today. It is free to use and can be installed on the network as an extension

How to use Baidu Netdisk app How to use Baidu Netdisk app Mar 27, 2024 pm 06:46 PM

Cloud storage has become an indispensable part of our daily life and work nowadays. As one of the leading cloud storage services in China, Baidu Netdisk has won the favor of a large number of users with its powerful storage functions, efficient transmission speed and convenient operation experience. And whether you want to back up important files, share information, watch videos online, or listen to music, Baidu Cloud Disk can meet your needs. However, many users may not understand the specific use method of Baidu Netdisk app, so this tutorial will introduce in detail how to use Baidu Netdisk app. Users who are still confused can follow this article to learn more. ! How to use Baidu Cloud Network Disk: 1. Installation First, when downloading and installing Baidu Cloud software, please select the custom installation option.

How to use NetEase Mailbox Master How to use NetEase Mailbox Master Mar 27, 2024 pm 05:32 PM

NetEase Mailbox, as an email address widely used by Chinese netizens, has always won the trust of users with its stable and efficient services. NetEase Mailbox Master is an email software specially created for mobile phone users. It greatly simplifies the process of sending and receiving emails and makes our email processing more convenient. So how to use NetEase Mailbox Master, and what specific functions it has. Below, the editor of this site will give you a detailed introduction, hoping to help you! First, you can search and download the NetEase Mailbox Master app in the mobile app store. Search for "NetEase Mailbox Master" in App Store or Baidu Mobile Assistant, and then follow the prompts to install it. After the download and installation is completed, we open the NetEase email account and log in. The login interface is as shown below

How to implement dual WeChat login on Huawei mobile phones? How to implement dual WeChat login on Huawei mobile phones? Mar 24, 2024 am 11:27 AM

How to implement dual WeChat login on Huawei mobile phones? With the rise of social media, WeChat has become one of the indispensable communication tools in people's daily lives. However, many people may encounter a problem: logging into multiple WeChat accounts at the same time on the same mobile phone. For Huawei mobile phone users, it is not difficult to achieve dual WeChat login. This article will introduce how to achieve dual WeChat login on Huawei mobile phones. First of all, the EMUI system that comes with Huawei mobile phones provides a very convenient function - dual application opening. Through the application dual opening function, users can simultaneously

How to use Xiaomi Auto app How to use Xiaomi Auto app Apr 01, 2024 pm 09:19 PM

Xiaomi car software provides remote car control functions, allowing users to remotely control the vehicle through mobile phones or computers, such as opening and closing the vehicle's doors and windows, starting the engine, controlling the vehicle's air conditioner and audio, etc. The following is the use and content of this software, let's learn about it together . Comprehensive list of Xiaomi Auto app functions and usage methods 1. The Xiaomi Auto app was launched on the Apple AppStore on March 25, and can now be downloaded from the app store on Android phones; Car purchase: Learn about the core highlights and technical parameters of Xiaomi Auto, and make an appointment for a test drive. Configure and order your Xiaomi car, and support online processing of car pickup to-do items. 3. Community: Understand Xiaomi Auto brand information, exchange car experience, and share wonderful car life; 4. Car control: The mobile phone is the remote control, remote control, real-time security, easy

How to implement the WeChat clone function on Huawei mobile phones How to implement the WeChat clone function on Huawei mobile phones Mar 24, 2024 pm 06:03 PM

How to implement the WeChat clone function on Huawei mobile phones With the popularity of social software and people's increasing emphasis on privacy and security, the WeChat clone function has gradually become the focus of people's attention. The WeChat clone function can help users log in to multiple WeChat accounts on the same mobile phone at the same time, making it easier to manage and use. It is not difficult to implement the WeChat clone function on Huawei mobile phones. You only need to follow the following steps. Step 1: Make sure that the mobile phone system version and WeChat version meet the requirements. First, make sure that your Huawei mobile phone system version has been updated to the latest version, as well as the WeChat App.

How to use spaces correctly in Go How to use spaces correctly in Go Mar 29, 2024 pm 03:42 PM

Go language is a simple, efficient, and highly concurrency programming language. It is an open source language developed by Google. In the Go language, the use of spaces is very important, it can improve the readability and maintainability of the code. This article will introduce how to use spaces correctly in Go language and provide specific code examples. Why you need to use spaces correctly In the programming process, the use of spaces is very important for the readability and beauty of the code. Appropriate use of spaces can make code clearer and easier to read, thus reducing

See all articles