spark sql pagination

12/2/2017 - 8:18 AM

Rendered
Source

use limit

select gender, smoking row_number() over (ORDER BY 1) as rowno from window_func_test limit 10;

+---------+----------+--------+--+
| gender  | smoking  | rowno  |
+---------+----------+--------+--+
| 女       | 不抽烟      | 1      |
| 男       | 不抽烟      | 2      |
| 女       | 不抽烟      | 3      |
| 男       | 不抽烟      | 4      |
| 男       | 不抽烟      | 5      |
| 男       | 不抽烟      | 6      |
| 女       | 1包以上/日   | 7      |
| 男       | 不抽烟      | 8      |
| 女       | 不抽烟      | 9      |
| 女       | 不抽烟      | 10     |
+---------+----------+--------+--+

OVER (PARTITION BY ... ORDER BY ... frame_type BETWEEN start AND end)

select gender, smoking, rowno from (select gender, smoking, row_number() over (ORDER BY 1) as rowno from window_func_test) where rowno between 1 and 5;

+---------+----------+--------+--+
| gender  | smoking  | rowno  |
+---------+----------+--------+--+
| 女       | 不抽烟      | 1      |
| 男       | 不抽烟      | 2      |
| 女       | 不抽烟      | 3      |
| 男       | 不抽烟      | 4      |
| 男       | 不抽烟      | 5      |
+---------+----------+--------+--+

demo

SELECT CAST(`年龄` AS STRING) AS `年龄`,CAST(`备注` AS STRING) AS `备注`,CAST(`日期` AS STRING) AS `日期`,rowno 
FROM (SELECT CAST(`47f962665b33420993a98ddcb633fd0c` AS STRICAST(`ba14978929064219a452fc426041918e` AS STRING) AS `备注`,CAST(`2301023901414ddfa7f015eadd533448` AS STRING) AS `日期`,row_number() over (ORDER BY 1) as rowno FROM 3beab33d94b14fbda27abf41df39ac1b) `3beab33d94b14fbdbf41df39ac1b` where rowno between 1 and 5;

join demo

SELECT `A.提交答卷时间`,`B.来自ip` from (SELECT CAST(`A`.`提交答卷时间` AS STRING) AS `A.提交答卷时间`,CAST(`B`.`来自ip` AS STRING) AS `B.来自ip`,row_number() over (ORDER BY 1) as rowno FROM ((SELECT CAST(`631ec92dc85642fe8d21d139f8dc8371` AS STRING) AS `提交答卷时间`,CAST(`bf84ae8c40554127a7c8db974b9fd8cf` AS STRING) AS `来自ip`,CAST(`cd353df762194ed8804fbe86f06040fa` AS STRING) AS `身份证号码`,CAST(`41ce1695ba784ec2bbf7414d1abaa31f` AS STRING) AS `性 别`,CAST(`af8312e218b74cb690eb6381aebf3ea3` AS STRING) AS `出生年月`,CAST(`249b834824ee49c5a318a2a754bd533c` AS STRING) AS `age`,CAST(`d77fa1af76364d7ea98aac6c10258cbc` AS STRING) AS `身高`,CAST(`5d0b25d17b0e4598a135fddfa964155d` AS STRING) AS `体重`,CAST(`1349da721cfb4ef3b621c04fa3ad2441` AS STRING) AS `居住地址`,CAST(`4891aa0ab02e47e9bf8d8618d7e8d673` AS STRING) AS `地区`,CAST(`282b37f5b7ce424baf5d401ded00f233` AS STRING) AS `省份`,CAST(`cfbe01e139614475ad4223a68297b9c5` AS STRING) AS `城市`,CAST(`a2b4dcdb64d24f88a5a29c319c2ea914` AS STRING) AS `区县`,CAST(`f22a0a222e77423db6e4d8f08a5db456` AS STRING) AS `您的教育程度`,CAST(`8e966035942944b5be1b3d1dd612aa07` AS STRING) AS `肺部疾病情况`,CAST(`8464cee3f8fb4560a4e4b3e1d81275a1` AS STRING) AS `疾病存在时间`,CAST(`c8c7a760d5e046a1b08b17d09ac3d991` AS STRING) AS `抽烟史`,CAST(`7ebc96ed6ec84e9e8fa57028316936d2` AS STRING) AS `请填写烟龄`,CAST(`8bd38971ade843b78269cbb387a6e55e` AS STRING) AS `请填写戒烟时间`,CAST(`abe32b9d97a84101ab475de60dd7e85b` AS STRING) AS `既往抽烟史`,CAST(`0d121699471144acb3450563052ac6ee` AS STRING) AS `既往烟龄多久`,CAST(`82a071b0206c41f7a57a34458442181e` AS STRING) AS `环境因素`,CAST(`599423df360e447db8ef37fff2863a1d` AS STRING) AS `接触上述环境的时间`,CAST(`e3102344f3db45ec9ab37e19034dec44` AS STRING) AS `肺部结节 情况`,CAST(`6470fdaaf3bd4b3db14b2e977a1dbf0c` AS STRING) AS `结节数量`,CAST(`71e16abb09f1458db8200c0dc5a785eb` AS STRING) AS `结节大小`,CAST(`8eb97d35a0e44bb9a3aaa9ce1bf20043` AS STRING) AS `发现时间`,CAST(`aae1903ef6204ccf8347d1b5d86aac7f` AS STRING) AS `结节增长情况`,CAST(`6aa8f0bcbe5148638e10ad2c6a08538d` AS STRING) AS `伴随什么症状`,CAST(`75ab664e9dec437e900039b910cba2f5` AS STRING) AS `体重下降周期`,CAST(`f1f76a649852427fb08f39aa562dd679` AS STRING) AS `下降公斤数`,CAST(`b6cf2cefe2cc41128286905cec0899e6` AS STRING) AS `肿瘤家族史`,CAST(`501a21ceccc4483099cdfed26903e5d4` AS STRING) AS `家族中哪代发病`,CAST(`3936b97f21284278a349710c35651c4a` AS STRING) AS `家族发病人数`,CAST(`eabd6405492b49578f3962ff50eb631c` AS STRING) AS `您的饮食习惯`,CAST(`2b08d85c9a6c41e3ad7bf6b3fd42dc30` AS STRING) AS `饮酒习惯`,CAST(`4e8eb61187254100a2f305a2bc19dfa3` AS STRING) AS ` 饮酒的平均酒量`,CAST(`cac2b62c40ae431dadefd67a273c708a` AS STRING) AS `饮酒年限`,CAST(`84427444067d4eec9372e4b047cf0705` AS STRING) AS `每年喝醉次数`,CAST(`7e25dfc3e5744f73a56da998373670de` AS STRING) AS `是否服用二甲 双胍`,CAST(`d382dc956c694b9089b29ed00ea45649` AS STRING) AS `服用二甲双胍的时间`,CAST(`78b79e88a47f4cd39feea8ae983dfc2b` AS STRING) AS `服用二甲双胍的剂量`,CAST(`faefd976915d4fe891a603e79892fe43` AS STRING) AS `是否服 用阿司匹林`,CAST(`a45d4c232e7d4e64b5f80e11e6d71087` AS STRING) AS `服用阿司匹林的剂量`,CAST(`1535f97cd28a43fca253aa2c8c24cf2b` AS STRING) AS `服用阿司匹林的时间`,CAST(`17156823824a4c33a00c7c3bed69ff1a` AS STRING) AS ` 大便情况`,CAST(`c65a295f89a34565847f4a1c4663799f` AS STRING) AS `慢性肠道疾病史`,CAST(`d7d03553a6564e0d90c9a5b539c4c686` AS STRING) AS `请选择疾病的持续时间`,CAST(`e621fa66654c403e8b3c21aa3d7b8a10` AS STRING) AS `阑尾 切除病史`,CAST(`dce2be28625a42e09ccc0e44c00fcb77` AS STRING) AS `胆囊切除病史`,CAST(`bb5b7d43d6a744e9b42b11427a365d88` AS STRING) AS `2型糖尿病病史`,CAST(`e555e4e2e6cf41efbd8d29d354b9a8c1` AS STRING) AS `慢性胆囊炎`,CAST(`3d723bb4cbbb4fdcbb793f74e094d489` AS STRING) AS `慢性阑尾炎病史`,CAST(`5eeb53f6168a415db7881ff04a94123e` AS STRING) AS `您的体检频率`,CAST(`97b1287192e24d7c91459020eaea4bde` AS STRING) AS `结果`,CAST(`e02be0bd50634f4f85c58a07182935ea` AS STRING) AS `计分` FROM 3668ab4272b24aada2a3f37e24451775) `A` INNER JOIN (SELECT CAST(`5eeee9765aa24a0dbdfcb28078792927` AS STRING) AS `提交答卷时间`,CAST(`8101330801f3475ab88372908208a158` AS STRING) AS `来自ip`,CAST(`3ef36e2594144d50a3233ddc3f58ceaf` AS STRING) AS `身份证号码`,CAST(`89eeae5027a943168982bbeee0ee5ec5` AS STRING) AS `性别`,CAST(`8739280d7e604fde82d888433c5d629a` AS STRING) AS `出生年月`,CAST(`810c6ad0c9764d0bb2a7a11bd2a69d22` AS STRING) AS `age`,CAST(`5c5fab34b1a941abb9be4abdeea63c8d` AS STRING) AS `身高`,CAST(`c3c9555dc54145508af3ab3d608e2fc2` AS STRING) AS `体重`,CAST(`d32dee1761be477889929a4b3f8d0889` AS STRING) AS `居住地址`,CAST(`ca24c613e02149569589808b7211a57a` AS STRING) AS `地区`,CAST(`b647979640c942699b2bf980764ee60e` AS STRING) AS `省份`,CAST(`73f7684a297d41d2b4d05f0ac8752b63` AS STRING) AS `城市`,CAST(`62cbfdc31fc3402aa6083273c0d2980b` AS STRING) AS `区县`,CAST(`c361aac11d4e4c07bae0ca7a08455b26` AS STRING) AS `您的教育程度`,CAST(`9307bdb925194b5fbf1e3767f2f00484` AS STRING) AS `肺部疾病情况`,CAST(`121346a98b59439e98fe32d02f84217f` AS STRING) AS `疾病存在时间`,CAST(`a320e86309944cf2ac66d301e3c2f3da` AS STRING) AS `抽烟史`,CAST(`a634f96c676445848a3876bfcfeb80f4` AS STRING) AS `请填写烟龄`,CAST(`c64912382b654fd3b6fbd3fefe70e8c3` AS STRING) AS `请填写戒烟时间`,CAST(`b08accf7f75f494e9a971a8594e5ba22` AS STRING) AS `既往抽烟史`,CAST(`f4f4fdab59604465907da8681926e41a` AS STRING) AS `既往烟龄多久`,CAST(`13871f169d74466585cfcb41e080cb9d` AS STRING) AS `环境因 素`,CAST(`de9aaf589ce74a1da283fabc153a97c7` AS STRING) AS `接触上述环境的时间`,CAST(`32caa0bef8764ff0b1e7ca9328b49ed5` AS STRING) AS `肺部结节情况`,CAST(`5d915a4c336f4e25855fae6702295749` AS STRING) AS `结节数量`,CAST(`4bb4dfc290f74edaaf65b72cef54e679` AS STRING) AS `结节大小`,CAST(`c1645535809749b48ce58f4378f9e7f7` AS STRING) AS `发现时间`,CAST(`c8da8053291344718980cef808f4b711` AS STRING) AS `结节增长情况`,CAST(`b37e5da71eff402e9895b8192ad28019` AS STRING) AS `伴随什么症状`,CAST(`3a82856aa6504fe09e21484175e414c2` AS STRING) AS `体重下降周期`,CAST(`dff0d63b682f4fdc8649022a7a51207b` AS STRING) AS `下降公斤数`,CAST(`7523660b847d4e72a2ed97007c55cf7f` AS STRING) AS `肿瘤家族史`,CAST(`7a98425e3f9d4604a5b42d99506f0563` AS STRING) AS `家族中哪代发病`,CAST(`e20fc157af67434f87586da270f924b6` AS STRING) AS `家族发病人数`,CAST(`e68c78fbc8ea48e399b009d292e06899` AS STRING) AS `您的饮食习惯`,CAST(`f9214096f7954e329031a8e541c90888` AS STRING) AS `饮酒习惯`,CAST(`2d11b4f7c0ab4bebb653f419abe5812e` AS STRING) AS `饮酒的平均酒量`,CAST(`6ad7e1d10f974246a8ed807c0a0080ff` AS STRING) AS `饮酒年限`,CAST(`f5b1f87427444545a6377f43a229c6cd` AS STRING) AS `每年喝醉次数`,CAST(`e407ee7ca23c4b3996a287e78288bed0` AS STRING) AS `是否服用二甲双胍`,CAST(`76634b2095974d249175e3f942aaab49` AS STRING) AS `服用二甲双胍的时 间`,CAST(`f3b5680cc98a4a3d91b51200e393baa0` AS STRING) AS `服用二甲双胍的剂量`,CAST(`135f60eb51564b5ebaf92ef20640a919` AS STRING) AS `是否服用阿司匹林`,CAST(`108411a38ea8460a8526eb66155aeb2b` AS STRING) AS `服用阿司匹 林的剂量`,CAST(`1af6620eb5b6434abf13a35c25440f38` AS STRING) AS `服用阿司匹林的时间`,CAST(`b22b67335cf14842901b6c7a5e6b505f` AS STRING) AS `大便情况`,CAST(`e2c084d04b8a409581fe9d74b137dc28` AS STRING) AS `慢性肠道疾病 史`,CAST(`b6e1f791ddd84970b24f32e608be157b` AS STRING) AS `请选择疾病的持续时间`,CAST(`9876f6863f7d40c4b54df55ba004bb70` AS STRING) AS `阑尾切除病史`,CAST(`bdf707472c884ce5980fba43364a5e0c` AS STRING) AS `胆囊切除病史`,CAST(`1577b070e61c4298a93667d79a8dd5c8` AS STRING) AS `2型糖尿病病史`,CAST(`a7c430416b994a5181165b855039e818` AS STRING) AS `慢性胆囊炎`,CAST(`1a7257974296485bb0fbdec281834941` AS STRING) AS `慢性阑尾炎病史`,CAST(`5fbb8f15bbfc4d3799c13467083c1484` AS STRING) AS `您的体检频率`,CAST(`6ef8b37fc70c4de6860eb7ceb1208d22` AS STRING) AS `结果`,CAST(`70b2035fefd34123a2e4216a9cc44031` AS STRING) AS `计分` FROM 7803426da8de46e7891c80d56f9f2cf8) `B` ON CAST(`A`.`身份证号码` AS STRING)=CAST(`B`.`身份证号码` AS STRING))) where rowno between 1 and 5;

+----------------------+-----------------+--+
|       A.提交答卷时间       |     B.来自ip      |
+----------------------+-----------------+--+
| 2015/06/08 07:50:17  | 27.154.125.255  |
| 2015/06/08 07:50:27  | 27.154.125.255  |
| 2015/06/08 07:50:28  | 27.154.125.255  |
| 2015/06/08 07:50:58  | 27.154.125.255  |
| 2015/06/08 07:51:01  | 27.154.125.255  |
+----------------------+-----------------+--+

Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

spark sql pagination

use limit

OVER (PARTITION BY ... ORDER BY ... frame_type BETWEEN start AND end)

demo

join demo

Reference