pseudo()

Read(1754) Label: pseudo table,

Here are how to use pseudo() functions.

pseudo()

Description:

Generate a pseudo table definition object.

Syntax:

pseudo(pd,n)

Note:

The function generates a pseudo table definition object by specifying the definition record pd. By setting up parameter n, you can get a multicursor from the pseudo table object; n is the number of subcursors in a multicursor.

Parameter:

pd

The pseudo table definition record, which is a table sequence record having the specified structure. Fields of the record and their descriptions are listed below:

 

file

Source of data in the pseudo table, which can be a composite table or a multizone composite table. This attribute is indispensable. The file path can be a relative path, which is relative to the main directory, or an absolute path.

 

zone

A list of zone table numbers when attribute file is a multizone composite table.

 

var

The table sequence/in-memory table variable name; an in-memory pseudo table will be generated when attribute file isn’t defined.

 

date

Zone column expression, which is valid only for a multizone composite table and which is generally a date, time or datetime field. In a multizone composite table, data is stored in multiple zone tables according to a time type field, and each zone table stores data in a certain period of time; such a time type field is called zone column expression, which is defined to make data filtering more convenient.

It is required that date field be ordered by the multiple files that are involved, but it is not required that the field be ordered in each single file; the interval of data values in each file is recorded when creating the pseudo table, and the target file will be automatically located during the query or filtering.

 

column

User-defined field definition, which is one or more table sequence records having the specified structure. Fields of the record and their descriptions are listed below:

 

 

name

The name of a real field, which is a field of the physical table; we define pseudo table fields according to this attribute.

We can use a pseudo field in the filtering expression; the pseudo table will convert the pseudo field computation to a real field computation, during which the pseudo field involved and the conversion method are defined through the other attributes; compute the pseudo field if they cannot be converted. For append/update operation, the corresponding real field values will be computed according to the related attributes.

 

 

alias

The list of pseudo field aliases. When its value is a string, it is alias of the binary dimension pseudo field or the real field; can set up multiple values to represent multiple aliases; when its value is an expression, return the result of computing the current expression.

 

 

enum

Enumerated pseudo field name, whose values are members of a list. You can use ==, != and contain in an enum filtering expression.

 

 

list

The list of values of enumerated pseudo field. Real field values are ordinal numbers and pseudo field values are members of this list.

 

 

bits

Binary dimension pseudo field names. In order to reduce storage usage, we can use a binary field (whose value is 1 or 0) to store Boolean field values; when there are many binary fields, we can use a binary dimension field to store them; a binary dimension field can store 32 binary fields at most. The definition arranges fields under Bools from low order to high order. Values of each field are bool value true or false.

 

 

exp

The expression of a real field’s alias used for append/update operation.

The expression of a redundant field. When no pseudo field name is specified but there is attribute exp in an expression, just take the redundant field as value of exp.

Use this expression to compute the current field during the append/update operation.

n

Number of subcursors in a multicursor; get a unicursor by default

Option:

@v

Enable to use pure-table-sequence based column-wise computation on a pseudo table generated from a composite table

Return value:

Pseudo table object

Example:

When data source of the pseudo table is a composite table:

 

A

 

1

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

Define a pseudo table definition record, where the pseudo table’s data source is a composite table

2

=pseudo(A1)

Generate a pseudo table object

3

=A2.import()

Get a table sequence from the pseudo table

 

When data source of the pseudo table is a multizone composite table:

 

A

 

1

=create(file,zone,date).record(["pseudo/OrderInfo.ctx",[1,2],"OTime"])

Define a pseudo table definition record, where the pseudo table’s data source is a multizone composite table; OTime field in the two zone tables 1.OrderInfo.ctx and 1.OrderInfo.ctx is time type; the field corresponds data in the year 2020 and 2021

2

=pseudo(A1)

Generate a pseudo table object

3

=A2.select(OTime>date(2020,12,20))

Filter data according to the zone column and the program automatically gets data from the corresponding zone table

4

=A3.import()

Return the filtering result as a table sequence

 

Setting parameter n:

 

A

 

1

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

 

2

=pseudo(A1,3)

Generate a pseudo table object where parameter n is 3

3

=A2.cursor()

Get a multicursor having 3 subcursors from the pseudo table

 

When using @v option:

 

A

 

1

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

 

2

=pseudo@v(A1)

Generate a pseudo table object from a composite table and use @v option to enable pure-table-sequence-based, column-wise computation

3

=A2.cursor()

Generate a column-wise cursor

4

=A2.import()

Generate a pure table sequence

5

=A2.memory()

Generate a column-wise, in-memory table

 

For an in-memory pseudo table:

 

A

 

1

>tab=demo.query("select * from CITIES")

Get CITIES table and store it in variable tab

2

=create(var).record(["tab"])

Use an in-memory table to define a pseudo table

3

=pseudo(A2)

Generate a pseudo table object

4

=A3.select(STATEID==3)

Get the information where STATEID is 3 from the pseudo table

5

=A4.import()

 

Define pseudo table’s user-defined fields:

Below is data in emps.ctx:

In the data file, the range of DeptID field values is 1-5, which corresponds to five departments – Sales, Technology, R&D, Financial and Admin.

Bools field stores data of two binary fields – Gender and Married. In field Gender, 0 represents male and 1 represents female; In field Married, 0 represents single and 1 represents married. Bools field values are results of computing bits(Gender,Married).

 

A

 

1

=create(file,column).record(["pseudo/emps.ctx",[{name:"DeptID",enum:"Dept",list:["Sales","Technology","R&D","Financial","Admin"]},

{name:"Bools",bits:["IfMarried","IfLady"]}]])

Bools Define a pseudo table definition record, which contains a enumerated pseudo field Dept and a binary dimeniosn pseudo field

2

=pseudo(A1)

Generate a pseudo table object

3

=A2.import(DeptID,Dept)

DeptID field values 1-5 correspond to  ["Sales","Technology","R&D","Financial","Admin"] respectively

4

=A2.import(Gender,Married,IfMarried,IfLady)

View the correspondence between Gender/Married and IfLady/IfMarried:

 

Define pseudo table’s user-defined fields – the redundant field:

Below is data of Details.ctx:

custInfoComment is a redundant field that stores values of custInfo.comment field:

 

A

 

1

=create(file,column).record(["pseudo/Details.ctx",[{name:"custInfoComment",exp:"custInfo.comment"}]])

Define exp:"custInfo.comment" for the pseudo table and its corresponding real field custInfoComment

2

=pseudo(A1)

Generate a pseudo table object

3

=A2.select(pos(custInfo.comment,"risk"))

As the filtering condition contains an expression, instead of retrieving custInfo records and then their fields, custInfo will be replaced by the real field custInfoComment

4

=A3.import()

Import the filttering result

5

=file("pseudo/details_new.btx").cursor@b(id,ddate,device,amt,bools,custInfo)

Below is data of details_new.btx:

6

=A2.append(A5)

Append A5’s data to A2, during which the program will automatically convert and generate custInfoComment

7

=A3.import()

Data of details.ctx after data appending:

 

Define pseudo table’s user-defined fields – field aliases:

Below is data of Events.ctx:

Each row of data is an event. Each event stores eventType and the corresponding property information eventInfo.

eventType field values are 1-3, which correspond to appInstall, appStart and append.

eventInfo contains records that also contain multiple fields for storing different properties.

s1, s2, f1 and dt1 are real fields that store the properties of the three types of events. s1 stores three string properties of the three types of events – browser, page and page; s2 stores three string properties of the three types of events – device, title and title; f1 stores two numeric properties – reward and amount, for appInstall event and append event; dt1 stores two datetime porperties – dt and dt for appInstall event and append event.

 

A

 

1

=create(file,column).record(["pseudo/Events.ctx",[{name:"eventType",alias:null,enum:"eventTypeString",list:["appInstall","appStart","appEnd"],exp:null},

{name:"s1",alias:["browser","appStart_page","appEnd_page"],enum:null,list:null,exp:"case(eventType,1:eventInfo.browser,2:eventInfo.page,3:eventInfo.page)"},

{name:"s2",alias:["device","appStart_title","appEnd_title"],enum:null,list:null,exp:"case(eventType,1:eventInfo.device,2:eventInfo.title,3:eventInfo.title)"},

{name:"f1",alias:["reward","amount"],enum:null,list:null,exp:"case(eventType,1:eventInfo.reward,3:eventInfo.amount)"},

{name:"dt1",alias:["appStart_dt","appEnd_dt"],enum:null,list:null,exp:"case(eventType,2:eventInfo.dt,3:eventInfo.dt)"}]])

在虚表的定义中为每个真字段配置多个有业务含义的别名。

真字段 s1 的三个别名 "browser","appStart_page","appEnd_page",以及对应的计算式 exp:"case(eventType, 1:eventInfo.browser, 2: eventInfo.page, 3: eventInfo.page)"

eventType 取值如果不是 12 或者 3,真字段 s1 应该为 null。所以这里不能写成 case(eventType, 1:eventInfo.browser;eventInfo.page)

s2f1dt1 的写法和 s1 类似。

2

=pseudo(A1)

生成虚表对象

3

=A2.select(eventTypeString=="appInstall" && browser=="firefox")

过滤条件中出现了字段别名,SPL 并不会取出 eventInfo 的记录再取其中的字段,而是自动替换成真实字段 s1,用冗余字段的值完成后续计算。

4

=A3.import(id,eventTypeString,browser,reward)

取出过滤结果

5

=file("pseudo/events_new.btx").cursor@b(id,eventTypeString,eventInfo)

events_new.btx数据如下:

6

=A2.append(A5)

追加数据。系统会根据eventTypeStringeventInfo自动生成 s1f1 等。

7

=A2.import()

追加数据后的Events.ctx

T.pseudo(T ’ ,…)

Description:

Segment a pseudo table according to the segmentation way of another.

Syntax:

T.pseudo(T’,…)

Note:

The function sets up pseudo table T’,… according to pseudo table T. The former is segmented the same way as the latter. After segmentation way is set up for the new pseudo table, you can retrieve a multicursor from this object.

Parameter:

T

A segmented pseudo table object

T’

A pseudo table object to be segmented

Example:

 

A

 

1

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

 

2

=pseudo(A1,3)

Generate a pseudo table object having 3 subcursors from a composite table

3

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

 

4

=pseudo(A3)

Generate a pseudo table object

5

=A2.pseudo(A4)

Segment pseudo table A4 the same way as pseudo table A2

6

=A4.cursor()

Get a multicursor from pseudo table A4