Shulou(Shulou.com)06/03 Report--

Data preparation

Cookie1,2015-04-10.

Cookie1,2015-04-11. 5.

Cookie1,2015-04-127.7

Cookie1,2015-04-13pr 3

Cookie1,2015-04-14pl 2

Cookie1,2015-04-15pm 4

Cookie1,2015-04-16pm 4

Create databases and tables

Create database if not exists cookie

Use cookie

Drop table if exists cookie1

Create table cookie1 (cookieid string, createtime string, pv int) row format delimited fields terminated by','

Load data local inpath "/ home/hadoop/cookie1.txt" into table cookie1

Select * from cookie1

SUM function

Select

Cookieid

Createtime

Pv

Sum (pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1

Sum (pv) over (partition by cookieid order by createtime) as pv2

Sum (pv) over (partition by cookieid) as pv3

Sum (pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4

Sum (pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5

Sum (pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6

From cookie1

explain

Pv1: the accumulation of pv in the group from the starting point to the current line, for example, the pv of the pv+11 number of pv1=10 number 11, 12 = 10 + 11 + 12

Pv2: same as pv1

Pv3: accumulation of all pv within the cookie1

Pv4: current line + forward 3 lines in the group, for example, 11 = 10 + 11, 12 = 10 + 11 + 12, 13 = 10 + 11 + 12 + 13, 14 = 11 + 12 + 13 + 14

Pv5: current line in the group + forward 3 lines + next 1 line, for example, 14 = 11 + 12 + 13 + 14 + 15 = 5, 7, 3, 2, 4, 21

Pv6: current line in the group + all subsequent lines, for example, 13 = 13 + 14 + 15 + 16 = 3, 2, 4, 4, 13, 14, 14, 14, 15, 16 = 2, 4, 4, 10.

Keyword interpretation

If you do not specify ROWS BETWEEN, the default is from the start point to the current line

If ORDER BY is not specified, all values within the group are accumulated

The key is to understand the meaning of ROWS BETWEEN, also known as the WINDOW clause:

PRECEDING: forward

FOLLOWING: back

CURRENT ROW: current Lin

UNBOUNDED: starting point

UNBOUNDED PRECEDING indicates that from the previous starting point

UNBOUNDED FOLLOWING: indicates to the following end point

-other AVG,MIN,MAX is used the same as SUM.

AVG function

Select

Cookieid

Createtime

Pv

Avg (pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,-- default is from start point to current line

Avg (pv) over (partition by cookieid order by createtime) as pv2,-- from the start to the current line, the result is the same as pv1

Avg (pv) over (partition by cookieid) as pv3,-- all lines within the group

Avg (pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4,-- current line + previous 3 lines

Avg (pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,-- current line + previous 3 lines + last 1 line

Avg (pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6-current line + all subsequent lines

From cookie1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.