Compatibility: Teradata Extension
The Sampling function (SAMPLE) permits a SELECT to randomly return rows from a Teradata database table. It allows the request to specify either an absolute number of rows or a percentage of rows to return. Additionally, it provides an ability to return rows from multiple samples.
The syntax for the SAMPLE function:
The next SELECT uses the SAMPLE to get a random sample of the sales table:SELECT * FROM student_course_tableSAMPLE 5; 5 Rows Returned
This next SELECT uses the SAMPLE function to request multiple samples to create a derived table (cover later). Then, the unique rows will be counted to show the random quality of the SAMPLE function:1 Row Returned
In the above execution, all five rows contained a different data value in the course ID. A second run of the same SELECT might very well yield these results:
1 Row Returned
Sometimes, a single sampling of the data is not sufficient. The SAMPLE function can be used to request more than one sample by listing either the number of rows or the percentage of the rows to be returned.
The next SELECT uses the SAMPLE function to request multiple samples:8 Rows Returned
Although multiple samples were taken, the rows came back as a single answer set consisting of 50% (.25 + .25) of the data. When it is necessary to determine which rows came from which sample, the SAMPLEID column name can be used to distinguish between each sample.
This SELECT uses the SAMPLE function with the SAMPLEID to request multiple samples and denote which sample each row came from:
Since the previous request asks for more rows than are currently in the table, a warning message 7473 is received. Regardless, it is only a warning and the SELECT works and all rows are returned. If there is any doubt in the number of rows, instead of using a fixed number and receiving the warning message, the use of percentage is a better choice.
The next SELECT uses the SAMPLE function with the SAMPLEID to request multiple samples as a percentage and denotes which sample each row came from:14 Rows Returned
Since SAMPLEID is a column, it can be used as the sort key.
The OLAP functions provide some very interesting and powerful functionality for examining and evaluating data. They provide an insight into the data that was not easily obtained prior to these functions.
Although they look like Aggregates, they are not normally compatible with them in the same SELECT list. As demonstrated here, aggregation can be performed, however, they must be calculated in a temporary or derived table.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.