select()

Read(2394) Label: select,

Here’s how to use select() functions.

A .select()

Description:

Return members of a sequence which satisfy a condition.

Syntax:

A.select(x)

 

A.select(x1:y1, x2:y2, ......xi:yi)

The simplified syntax of "&&" for a multiple-condition query, which is equal to A.select(x1== y1 && x2== y2 &&...... xi==yi)

Note:

The function computes expression x against each member of the sequence A, and returns a new sequence composed of those members which make x return true. When there are no parameters, it returns all the members. Note that if the name of a to-be-referenced column of the sequence is the same as a cell name, the sequence name should be attached before the column name in the expression.

Parameter:

A

A sequence

x

A Boolean expression, which can be null

xi:yi

xi is an expression, and yi is a comparing value

Option:

@1

Return the first member that fulfills the condition

@z

Search the members from back to front

@b

Use binary search, which requires that A is an ascendingly ordered sequence and that parameters are separated by colon or they are expressions returning numeric values; the eligible members are found when result of expressions are 0

@m

Use parallel algorithm to handle data-intensive or computation-intensive tasks; no definite order for the records in the result set; it can’t be used with @1bz options

@t

Return an empty table sequence with data structure if the grouping and aggregate operation over the sequence returns null

@c

Enable getting the eligible member(s) from left to right beginning from the first member until the first ineligible member appears

@r

Enable searching for the first eligible members from left to right and getting all members after it (inclusive) until the last one

@v

Return result as a pure table sequence when sequence A is a pure table sequence; return a pure sequence when this option is absent

Return value:

A sequence or a table sequence

Example:

 

A

 

1

[2,5,4,3,2,1,4,1,3]

 

2

=A1.select(~>3)

[5,4,4].

3

=A1.select@1(~>3)

5; return the first eligible member.

4

=demo.query("select EID,NAME,GENDER,DEPT,SALARY  from EMPLOYEE order by EID")

 

5

=A4.select(GENDER:"F",SALARY:7000)

Multi-condition query.

.

Specify search direction:

 

A

 

1

[2,5,4,3,2,1,4,1,3]

 

2

=A1.select(~>3)

[5,4,4].

3

=A1.select@z(~>3)

[4,4,5]; search backwards.

4

[8,10,3,5,7,9,11,13,7]

 

5

=A4.select@c(~>7)

Return [8,10]; search from the first member until the first ineligible member appears.

6

=A4.select@zc(~>6)

Return [7,13,11,9,7]; search backwards until the first ineligible member appears.

7

=A4.select@r(~>10)

Return [11,13,7]; Search for the first eligible member from the first member in order and get all members after it (inclusive).

 

High-efficiency search and filtering:

 

A

 

1

=demo.query("select EID,NAME,GENDER,DEPT,BIRTHDAY from employee")

 

2

=A1.select@m(GENDER=="F")

@m option enables parallel computation when there is a large volume of data to increase performance.

3

=A1.sort(EID)

Sort A1 by EID in the ascending order.

4

=A3.select@b(EID<10)

As A3 is an ascending sequence, here we use binary search to perform the query, for which parameter x should be a numeric expression.

 

When a column name and a cell name are same:

 

A

 

1

=to(3).new(~:ID,~*~:A1)

2

=A1.select(A1.A1==4)

As the column name and the cell name are same, the column name should be suffixed by sequence name when it is referenced in an expression.

 

Return a pure table sequence:

 

A

 

1

=demo.query("select EID,NAME,GENDER,DEPT,BIRTHDAY from employee").keys(EID)

 

2

=A1.i()

Convert table sequence A1 to a pure table sequence.

3

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

Return a pure sequence.

4

=A2.select@v(GENDER=="F")

@v option enables returning a pure table sequence.

Related function:

A.pselect()

T.select@i()

Description:

Return members of a table sequence which satisfy a condition.

Syntax:

T.select@i(x)

Note:

The function computes expression x against each member of table sequence T, and returns a new table sequence composed of those members which make x return true

 

T is a table sequence for which an index is already created. T.select@i() will reuse the table sequence’s index and order of records in the result set will probably be disrupted.

 

The function returns all members when parameter x is absent.

 

Parameter:

T

A table sequence for which an index is already created

x

Filtering expression; can be null

Return value:

A table sequence

Example:

 

A

 

1

=demo.query("select * from DEPT").keys@i(DEPTID)

Return a table sequence whose key is DEPTID and create the hash index for it.

 

2

=A1.select@i(FATHER==12)

Reuse the index created in A1 and return the record where FATHER is 12 in A1’s table sequence.

 

T.select()

Description:

Define a record filtering operation on a pseudo table and return a new pseudo table.

Syntax:

T.select(x)

 

Note:

The function defines a computation on pseudo table T, which will calculate expression x on each of its records and get records that make x true, and return a new pseudo table.

It returns a pseudo table retaining all records when parameter x is absent.

Parameter:

T

A pseudo table.

x

A Boolean expression that is a filter condition, which can be null.

Return value:

Pseudo table object

Example:

 

A

 

1

=create(file).record(["D:/file/pseudo/app.ctx"])

Below is data in composite table app.ctx:

2

=pseudo(A1)

 

Generate a pseudo table object.

3

=A2.select(eid>7)

Define a computation on A2’s pseudo table, which will get records that satisfy eid>7, and return a new pseudo table.

4

=A3.import()

Import data from A3’s pseudo table while executing the computation defined in A3 on A2’s pseudo table, and return the following table:

5

=A2.select()

Define a computation on A2’s pseudo table, which will get all records, and return a new pseudo table.

6

=A5.import()

Import data from A5’s pseudo table while executing the computation defined in A5 on A2’s pseudo table, and return the following table:

T.select( x;f )

Description:

Define a record filtering operation on a pseudo table, return a new pseudo table, and write ineligible records to a bin file.

Syntax:

T.select(x;f)

Note:

The function defines a computation on pseudo table T, which will compute expression x on each of its records, get records that make x return true and return them to a new pseudo table, and writes records that make x return false into bin file f.

Parameter:

T

A pseudo table.

x

A Boolean expression.

f

A bin file.

Return value:

  Pseudo table

Example:

 

A

 

1

=create(file).record(["EMPLOYEE.ctx"])

 

2

=pseudo(A1)

Generate a pseudo table from a composite table.

3

=file("emp_NOTHR.btx")

 

4

=A2.select(DEPT=="HR";A3)

Define a computation on A2’s pseudo table, which will get records that meet the condition DEPT=="HR" and return them to a new pseudo table, and write records not meeting the condition into bin file emp_NOTHR.btx in the main directory.

5

=A4.import()

Import data from A4’s pseudo table while executing the computation defined in A4 on A2’s pseudo table, and generate bin file emp_NOTHR.btx. Below is the data A5 returns:

ch. select()

Description:

Attach a record filtering computation to a channel and return the orignal channel.

Syntax:

ch.select(x)

Note:

The function attaches a computation to channel ch, which will calculate expression x on each of its records and get records that make values of x true, and returns the original channel ch.

When parameter x is absent, get all records.

This is an attachment function.

Parameter:

ch

A channel.

x

Boolean expression.

Return value:

  Channel

Example:

 

A

 

1

=demo.cursor("select * from SALES")

 

2

=channel()

Create a channel.

3

=A2.select(ORDERID>100)

Attach a computation to A2’s channel, which will get records meeting the condition ORDERID>100, and return the original channel.

4

=A2.fetch()

Fetch and keep the current data in the channel.

5

=A1.push(A2)

Be ready to push data in A1’s cursor to A2’s channel.

6

=A1.fetch()

Fetch data from cursor A1 while pushing data to channel A2 to execute the attached computation and keep the result.

7

=A2.result()

Get result from the channel.

ch .select( x,ch’ )

Description:

Send records in a channel that can’t meet the given condition into another channel.

Syntax:

ch.select(x,ch’)

Note:

The function calculates expression x against each of the records in channel ch, and sends those records over which the value of x is false into channel ch’.

Parameter:

ch

Channel

x

A Boolean expression

ch’

Channel

Return value:

  Channel

Example:

 

A

 

1

=demo.cursor("select EID,NAME,SALARY from EMPLOYEE " )

 

2

=channel()

Create a channel.

3

=channel()

Create a channel.

4

=A1.push(A2)

Push data in A1’s cursor into A2’s channel.

5

=A2.select(EID<5,A3)

Push records in A2’s channel that can’t meet the condition EID<5 into A3’s channel.

6

=A2.fetch()

Attach ch.fetch() function that gets the final result set to A2’s channel to fetch and store the existing data in A2’s channel.

7

=A3.fetch()

Attach ch.fetch() function that gets the final result set to A3’s channel to fetch and store the existing data in A3’s channel.

8

=A1.skip()

It is when fetching data from A1’s cursor begins that data in the cursor is truly pushed to the channel and computations start.

9

=A2.result()

Get result of the computation in channel A2.

10

=A3.result()

Get result of the computation in channel A3.

cs .select()

Description:

Attach the record filtering action to a cursor and return the original cursor.

Syntax:

cs.select(x)

Note:

The function attaches a computation to cursor cs, which will calculate expression x against each of the records in cursor cs and get records that make value of x true, and return the original cursor cs.

 

When parameter x is omitted, get all records of cursor cs. The function supports multicursors.

 

This is a delayed function.

Parameter:

cs

A cursor.

x

A Boolean value.

Return value:

  Cursor

Example:

 

A

 

1

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

Return a cursor:

2

=A1.select(STUDENTID>10)

Attach a computation to cursor A1, which will select records where STUDENTID is greater than 10 from the cursor, and return cursor A1:

3

=A1.fetch()

Fetch data from cursor A1 where A2’s computation is executed (it would be better that data is fetched in batches when a large amount of data is involved):

cs .select( x,ch’ )

Description:

Send records in a cursor that can’t meet the given condition into a channel.

Syntax:

cs.select(x,ch’)

Note:

The function calculates expression x against each of the records in cursor cs, and sends those records over which the value of x is false into channel ch’.

Parameter:

cs

A cursor.

x

A Boolean value.

ch’

A channel.

Return value:

  Channel

Example:

 

A

 

1

=demo.cursor("select EID,NAME,SALARY from EMPLOYEE" )

 

2

=channel()

Create a channel.

3

=A1.select(EID<5,A2)

Push cursor A1’s records not meeting condition EID<5 into A2’s channel.

4

=A2.fetch()

Fetch and store the existing data in the channel.

5

=A1.fetch()

Fetch data from the filtered cursor.

6

=A2.result()

Get result from the channel.

cs.select( x;f )

Description:

Attach the record filtering action to a cursor and return the original cursor while writing records that not satisfy the specified expression to a bin file.

Syntax:

cs.select(x;f)

Note:

The function attaches a record filtering action to cursor cs, which will compute expression x over each record of cursor cs, get records that make value of x true, return the original cursor cs and write the records that do not satisfy expression x into bin file f.

 

This is a delayed function.

Parameter:

cs

A cursor.

x

A Boolean expression.

f

A bin file.

Return value:

  Cursor

Example:

 

A

 

1

=demo.cursor("select * from dept")

Return a cursor:

2

=file("dept.btx")

Generate a bin file object.

3

=A1.select(DEPTID<5;A2)

Attach a computation to cursor A1, which will get records that meet DEPTID <5, return cursor A1 and write records that cannot meet the condition to bin file dept.btx:

4

=A1.fetch()

dept.btx will be really generated after data is really fetched out from cursor A1; below is data in cursor A1 where the computation is executed (it would be better that data is fetched in batches when data amount is large):