Searching and Locat ion

Read(200) Label: searching, location,

This chapter lists code examples about data searching and location, including Locate a member, Locate a sub-sequence, Locate members matching specified conditions and return their positions, Find members matching specified conditions from back to front, Stop searching after the first/all members matching specified conditions are found, Search from the Kth member, Query on multiple fields, Speed up query on sorted sequence/record sequence via binary search, Aggregates on query results, Filter a table sequence, Perform query on a table sequence/record sequence by primary key value, Get the record with max/min value and its position, and calculate link relative ratio for selected members.

Locate a member

 

A

 

1

[3,2,1,5,1]

 

2

=A1.pos(1)

3

3

=A1.pos@a(1)

[3,5]

 

Locate a member

 

A

 

1

[3,2,1,5,1]

 

2

=A1.pos(1)

3

3

=A1.pos@a(1)

[3,5]

 

Locate a sub - sequence

 

A

B

 

1

[1,2,3,4,5]

[4,5]

 

2

=A1.pos@c(B1)

 

4

 

Locate a sub - sequence

 

A

B

 

1

[1,2,3,4,5]

[4,5]

 

2

=A1.pos@c(B1)

 

4

 

Locate members matching specified conditions and return their positions

 

A

 

1

=demo.query("select EID,NAME, STATE, GENDER, BIRTHDAY,HIREDATE,DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(GENDER=="M")

Locate all members matching the specified condition

3

=A1.pselect(GENDER=="M")

Stop searching after the first matching member is found, and return the member position

 

Locate members matching specified conditions and return their positions

 

A

 

1

=demo.query("select EID,NAME, STATE, GENDER, BIRTHDAY,HIREDATE,DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(GENDER=="M")

Locate all members matching the specified condition

3

=A1.pselect(GENDER=="M")

Stop searching after the first matching member is found, and return the member position

 

Find members matching specified conditions from back to front

 

A

 

1

=demo.query("select EID, NAME,STATE, GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE")

 

2

=A1.select@z(GENDER=="M")

 

3

=A1.pselect@z(GENDER=="M")

 

 

Find members matching specified conditions from back to front

 

A

 

1

=demo.query("select EID, NAME,STATE, GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE")

 

2

=A1.select@z(GENDER=="M")

 

3

=A1.pselect@z(GENDER=="M")

 

 

Stop searching after the first/all members matching specified conditions are found

 

A

 

1

=demo.query("select NAME,EVENT, SCORE from GYMSCORE")

 

2

=A1.pselect(EVENT:"UnevenBars")

Stop searching after the first matching member is found

3

=A1(A2).SCORE

Scores on a specified event

4

=demo.query("select EID,NAME,STATE, GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE")

 

5

=A4.select(GENDER=="M")

Find all matching members

6

=A4.select@1(GENDER=="M")

Find the first matching member

7

=A4.pselect@a(GENDER=="M")

Locate all matching members

 

Stop searching after the first/all members matching specified conditions are found

 

A

 

1

=demo.query("select NAME,EVENT, SCORE from GYMSCORE")

 

2

=A1.pselect(EVENT:"UnevenBars")

Stop searching after the first matching member is found

3

=A1(A2).SCORE

Scores on a specified event

4

=demo.query("select EID,NAME,STATE, GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE")

 

5

=A4.select(GENDER=="M")

Find all matching members

6

=A4.select@1(GENDER=="M")

Find the first matching member

7

=A4.pselect@a(GENDER=="M")

Locate all matching members

 

Search from the K th member

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE")

 

2

=A1.pselect(GENDER=="M",8)

 

 

Search from the K th member

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE,DEPT, SALARY from EMPLOYEE")

 

2

=A1.pselect(GENDER=="M",8)

 

 

Query on multiple fields

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE, DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(GENDER:"M",DEPT:"R&D")

 

3

=A1.pselect(GENDER:"M",DEPT:"R&D")

 

4

=A1.pselect@a(GENDER:"M",DEPT:"R&D")

 

 

Query on multiple fields

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE, DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(GENDER:"M",DEPT:"R&D")

 

3

=A1.pselect(GENDER:"M",DEPT:"R&D")

 

4

=A1.pselect@a(GENDER:"M",DEPT:"R&D")

 

 

Speed up query on sorted sequence /record sequence via binary search

 

A

 

1

=demo.query("select * from EMPLOYEE order by GENDER,DEPT")

 

2

=A1.select@b(GENDER:"M",DEPT:"R&D")

 

3

=A1.pselect@b(GENDER:"M",DEPT:"R&D")

 

 

Speed up query on sorted sequence /record sequence via binary search

 

A

 

1

=demo.query("select * from EMPLOYEE order by GENDER,DEPT")

 

2

=A1.select@b(GENDER:"M",DEPT:"R&D")

 

3

=A1.pselect@b(GENDER:"M",DEPT:"R&D")

 

 

Aggregates on query results

 

A

 

1

=demo.query("select NAME,UNITPRICE,QUANTITY from RECEIPT")

 

2

=A1.select(NAME:"Apple").sum(UNITPRICE*QUANTITY)

 

 

Aggregates on query results

 

A

 

1

=demo.query("select NAME,UNITPRICE,QUANTITY from RECEIPT")

 

2

=A1.select(NAME:"Apple").sum(UNITPRICE*QUANTITY)

 

 

Filter a table sequence

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE, DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(SALARY<6300)

 

3

=A1.select(SALARY>5000)

 

 

Filter a table sequence

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER,BIRTHDAY, HIREDATE, DEPT, SALARY from EMPLOYEE")

 

2

=A1.select(SALARY<6300)

 

3

=A1.select(SALARY>5000)

 

 

Perform query on a table sequence/record sequence by primary key value

 

A

 

1

=demo.query("select * from SCORES")

 

2

=A1.keys(CLASS,STUDENTID)

 

3

=A1.pfind(["Class one",2])

 

4

=A1(A3)

 

5

=A1.find(["Class one",2])

 

 

Perform query on a table sequence/record sequence by primary key value

 

A

 

1

=demo.query("select * from SCORES")

 

2

=A1.keys(CLASS,STUDENTID)

 

3

=A1.pfind(["Class one",2])

 

4

=A1(A3)

 

5

=A1.find(["Class one",2])

 

 

Get the record with max/min value and its position

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ")

 

2

=A1.sort(HIREDATE)

Sort

3

=A2.pmax(BIRTHDAY)

The position of the youngest employee

4

=A2(to(A3-1))

The records of employees employed earlier

5

=A4.minp(BIRTHDAY).NAME

The name of the oldest employee

 

Get the record with max/min value and its position

 

A

 

1

=demo.query("select EID,NAME,STATE,GENDER, BIRTHDAY,HIREDATE,DEPT,SALARY from EMPLOYEE ")

 

2

=A1.sort(HIREDATE)

Sort

3

=A2.pmax(BIRTHDAY)

The position of the youngest employee

4

=A2(to(A3-1))

The records of employees employed earlier

5

=A4.minp(BIRTHDAY).NAME

The name of the oldest employee

 

Calculate link relative ratio for selected members

 

A

B

 

1

=demo.query("select * from STOCKRECORDS")

 

 

2

=A1.pselect@a(CLOSING>10)

 

Locate the DATEs on which the closing prices are greater than 10

3

=A1.calc(A2, CLOSING/CLOSING[-1]-1)

 

Calculate the corresponding increase

4

==A2.new(

A1(~).DATE:Date,

Output the result set

5

 

A1(~).CLOSING:ClosingPrice,

 

6

 

A3(#):Increase)

 

 

Calculate link relative ratio for selected members

 

A

B

 

1

=demo.query("select * from STOCKRECORDS")

 

 

2

=A1.pselect@a(CLOSING>10)

 

Locate the DATEs on which the closing prices are greater than 10

3

=A1.calc(A2, CLOSING/CLOSING[-1]-1)

 

Calculate the corresponding increase

4

==A2.new(

A1(~).DATE:Date,

Output the result set

5

 

A1(~).CLOSING:ClosingPrice,

 

6

 

A3(#):Increase)