CSO_SEARCH_STATS
This table will be populated hourly and daily from cso_stg_questions. Hourly rollup will be deleted at the end of every day once the day ends. At the end of every month, older daily rollup data will be purged.
Details
-
Schema: FUSION
-
Object owner: CSO
-
Object type: TABLE
-
Tablespace: Default
Primary Key
| Name | Columns |
|---|---|
|
CSO_SEARCH_STATS_PK |
SEARCH_STATS_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| SEARCH_STATS_ID | NUMBER | 18 | Yes | Primary key of the table. Populated by sequence number. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| STAT_DATE | DATE | Yes | The date on which the stats occurred. | ||
| STAT_HOUR | NUMBER | 9 | Yes | Applicable for window=1 and represents the HOUR of the DAY for which the data belongs to. | |
| NORMALIZED_QUESTION_ID | NUMBER | 18 | Yes | The unique ID used to track the text for a given normalized question. | |
| LOCALE_ID | VARCHAR2 | 32 | Yes | This is the reference to the locale in CSO_LOCALE table. | |
| QUESTION_COUNT | NUMBER | 10 | The question count is the number of questions asked. These questions can be actual or normalized questions. | ||
| RESPONSE_COUNT | NUMBER | 10 | The response count is the number of responses returned for a given question. The question can be an actual or normalized question. | ||
| WINDOW | NUMBER | 1 | Yes | 1-hourly, 2-daily, 3-monthly (rollup strategy). | |
| UNKNOWN_WORD_COUNT | NUMBER | 10 | Number of unknown words that are not configured in search application. | ||
| CONCEPT_MATCH_COUNT | NUMBER | 10 | Number of known words or concepts that are configured in search application. | ||
| DEPARTMENT_ID | VARCHAR2 | 32 | The unique ID for the department as stored by knowledge and will be used for striping questions. | ||
| CLICKTHROUGH_01_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the first position and is clicked against a question. | |
| CLICKTHROUGH_02_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the second position and is clicked against a question. | |
| CLICKTHROUGH_03_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the third position and is clicked against a question. | |
| CLICKTHROUGH_04_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the fourth position and is clicked against a question. | |
| CLICKTHROUGH_05_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the fifth position and is clicked against a question. | |
| CLICKTHROUGH_06_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the sixth position and is clicked against a question. | |
| CLICKTHROUGH_07_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the seventh position and is clicked against a question. | |
| CLICKTHROUGH_08_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the eighth position and is clicked against a question. | |
| CLICKTHROUGH_09_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the ninth position and is clicked against a question. | |
| CLICKTHROUGH_10_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the tenth position and is clicked against a question. | |
| CLICKTHROUGH_11_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the eleventh position and is clicked against a question. | |
| CLICKTHROUGH_12_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the twelfth position and is clicked against a question. | |
| CLICKTHROUGH_13_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the thirteenth position and is clicked against a question. | |
| CLICKTHROUGH_14_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the fourteenth position and is clicked against a question. | |
| CLICKTHROUGH_15_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the fifteenth position and is clicked against a question. | |
| CLICKTHROUGH_16_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the sixteenth position and is clicked against a question. | |
| CLICKTHROUGH_17_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the seventeenth position and is clicked against a question. | |
| CLICKTHROUGH_18_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the eighteenth position and is clicked against a question. | |
| CLICKTHROUGH_19_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the nineteenth position and is clicked against a question. | |
| CLICKTHROUGH_20_POSITION | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed is at the twentieth position and is clicked against a question. | |
| CLICKTHROUGH_21_PLUS | NUMBER | 10 | Yes | This value is incremented by 1 if the position of the answer displayed greater than the twentieth position and is clicked against a question. | |
| CLICKTHROUGH_COUNT | NUMBER | 10 | Yes | The count of answers clicked against a question. | |
| CLICKTHROUGH_POSITION | NUMBER | 10 | Yes | The position of each answer clicked against a question. | |
| QUESTIONS_WITH_CLICKS_COUNT | NUMBER | 10 | Yes | The value is set to 1 against a question if the question has at least one answer view else the value is set to 0. | |
| BU_ORG_ID | NUMBER | 18 | This is the reference to Business Unit Org Id. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| CSO_SEARCH_STATS | cso_normalized_questions | NORMALIZED_QUESTION_ID |
| CSO_SEARCH_STATS | cso_site | DEPARTMENT_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| CSO_SEARCH_STATS_FK1 | Non Unique | Default | NORMALIZED_QUESTION_ID |
| CSO_SEARCH_STATS_N1 | Non Unique | Default | WINDOW |
| CSO_SEARCH_STATS_PK | Unique | Default | SEARCH_STATS_ID |
| CSO_SEARCH_STATS_U1 | Unique | Default | BU_ORG_ID, DEPARTMENT_ID, STAT_DATE, STAT_HOUR, NORMALIZED_QUESTION_ID, LOCALE_ID, WINDOW |