1/114

Contents

Title and Copyright Information

Preface

• About this guide
• Audience
• Conventions
• Contacting Oracle Customer Support

1 Introduction to EQL

• EQL overview
• Important concepts and terms
• EQL and SQL: a comparison
• Query overview
• How queries are processed
• EQL reserved keywords

2 Statements and Clauses

• DEFINE clause
• RETURN clause
• LET clause
• SELECT clause
• AS clause
• FROM clause
• JOIN clause
• WHERE clause
• HAVING clause
• ORDER BY clause
• PAGE clause

3 Aggregation

• GROUP/GROUP BY clauses
• MEMBERS extension
• GROUPING SETS expression
• ROLLUP extension
• CUBE extension
• GROUPING function
• COUNT function
• COUNT_APPROX
• COUNTDISTINCT function
• APPROXCOUNTDISTINCT function
• Multi-level aggregation
• Per-aggregation filters

4 Expressions

• Supported data types
• Operator precedence rules
• Handling of literals and values
  • Character handling
  • Handling of upper- and lower-case
  • Handling NULL attribute values
  • Handling of NaN, inf, and -inf results
  • Integer type promotion
  • Handling of precision for doubles
• Functions and operators
  • Numeric functions
  • Aggregation functions
  • Geocode functions
  • Date and time functions
    • Manipulating current date and time
    • Constructing date and time values
    • Time zone manipulation
    • Using EXTRACT to extract a portion of a dateTime value
    • Using TRUNC to round down dateTime values
    • Using arithmetic operations on date and time values
  • String functions
  • Arithmetic operators
  • Boolean operators
• Using EQL results to compose follow-on queries
• Using LOOKUP expressions for inter-statement references
• ARB
• BETWEEN
• CASE
• COALESCE
• CORRELATION
• HAS_REFINEMENTS
• IN
• PERCENTILE
• RECORD_IN_FAST_SAMPLE

5 Sets and Multi-assign Data

• About sets
• Aggregate functions
  • SET function
  • SET_INTERSECTIONS function
  • SET_UNIONS function
• Row functions
  • ADD_ELEMENT function
  • CARDINALITY function
  • COUNTDISTINCTMEMBERS function
  • DIFFERENCE function
  • FOREACH function
  • INTERSECTION function
  • IS_EMPTY and IS_NOT_EMPTY functions
  • IS_MEMBER_OF function
  • SINGLETON function
  • SUBSET function
  • TRUNCATE_SET function
  • UNION function
• Set constructor
• Quantifiers
• Grouping by sets

6 EQL Use Cases

• Re-normalization
• Grouping by range buckets
• Manipulating records in a dynamically computed range value
• Grouping data into quartiles
• Combining multiple sparse fields into one
• Joining data from different types of records
• Linear regressions in EQL
• Using an IN filter for pie chart segmentation
• Running sum
• Query by age
• Calculating percent change between most recent month and previous month

7 EQL Best Practices

• Controlling input size
• Filtering as early as possible
• Controlling join size
• Additional tips

Index