Calling all Oracle Database developers! There's a new sample schema in town!
Customer Orders is a simple new schema for you to use in your presentations, demos, and blogs.
Why Have We Created a New Schema?
The current sample schemas (HR, SH, CO, PM, IX, & BI) were created in the early 2000s, back when 9i was still hip and new.
Nearly two decades and five major releases later, Oracle Database has improved significantly. And the development community has evolved too. JSON has replaced XML as the de facto data transfer format.
So we wanted a data set showing you how to use JSON in Oracle Database. While also highlighting other enhancements added over the years, such as identity columns.
Customer Orders requires Oracle Database 12c or higher.
While we could have added new features to one of the existing schemas, one of the key reasons for using them is they've become a standard you can rely upon. Everyone works from the same definition. Which means you can use the tables in your blog posts, scripts, and presentations without further explanation. Changing the existing schemas breaks this common reference point.
So we decided to have a fresh start. And Customer Orders was born!
What Is the New Schema?
Customer Orders models a simple retail application using these tables:
The sample data represents a basic clothing line. PRODUCTS.PRODUCT_DETAILS stores a JSON document describing each clothing item and its customer reviews. For example:
{
"colour" : "red",
"gender" : "Girl's",
"brand" : "BRANDNAME",
"description" : "description",
"sizes" : [
"1 Yr", "2 Yr", "3-4 Yr", "5-6 Yr", "7-8 Yr", "9-10 Yr"
],
"reviews" : [
{
"rating" : 9,
"review" : "Review text"
}
]
}
This allows you the flexibility to use this schema to store a wide range of products. All you need to do is add the relevant attributes to your JSON!
Where Can I Find Customer Orders?
Download the schema from the Oracle sample schemas GitHub repository.
How Do I Install Customer Orders?
To install customer orders, you must use Oracle Database 12.1.0.2 or higher.
Once you've downloaded the scripts, run co_main with the following parameters:
This will drop and recreate the user CO.
@co_main <CO_password> <connect string> <tablespace> <temp tablespace>
For example:
@co_main copassword localhost:1521/pdb USERS TEMP
If you want to install the tables in an existing schema, run the following scripts:
@co_ddl
@co_dml
What Can I Do with This Schema?
Whatever you like! Please use this to build demos, write scripts, and use in presentations.
Looking for inspiration?
The installation includes several views and sample queries to get your SQL juices flowing.
Here are a few examples:
Extract Product Reviews from PRODUCT_DETAILS JSON
The sample data include an array of reviews for each product in its JSON data.
Using 12c's JSON_table, you can extract these out to traditional rows-and-columns, like so:
select p.product_name, r.rating,
round (
avg ( r.rating ) over (
partition by product_name
),
2
) avg_rating,
r.review
from products p,
json_table (
p.product_details, '$'
columns (
nested path '$.reviews[*]'
columns (
rating integer path '$.rating',
review varchar2(4000) path '$.review'
)
)
) r;
Find High-Value Customers
The following uses 12c's row pattern matching to find all the people who placed at least £100 worth of orders for three consecutive months:
with rws as (
select
o.customer_id, trunc ( o.order_datetime, 'mm' ) order_month,
sum ( oi.quantity * oi.unit_price ) month_total
from products p
join order_items oi
on p.product_id = oi.product_id
join orders o
on oi.order_id = o.order_id
group by o.customer_id, trunc ( o.order_datetime, 'mm' )
)
select * from rws
match_recognize (
partition by customer_id
order by order_month
measures
count(*) as num_months,
sum ( month_total ) as total_value
pattern ( high_value consecutive{2,} )
define
high_value as
month_total >= 100,
consecutive as
order_month = prev ( add_months ( order_month, 1 ) )
and month_total >= 100
);
Product a Matrix of Sales Value by Month and Year
Finance teams often want sales broken down into a table with months across the top and years down the side.
The following shows you how to do this with the PIVOT clause:
with order_totals as (
select extract ( year from o.order_datetime ) order_year,
to_char ( o.order_datetime, 'MON', 'NLS_DATE_LANGUAGE = english' ) order_month,
sum ( oi.quantity * oi.unit_price ) value_of_orders
from orders o
join order_items oi
on o.order_id = oi.order_id
group by extract ( year from o.order_datetime ),
to_char ( o.order_datetime, 'MON', 'NLS_DATE_LANGUAGE = english' )
)
select * from order_totals
pivot (
sum ( value_of_orders ) value
for order_month in (
'JAN' JAN, 'FEB' FEB, 'MAR' MAR, 'APR' APR, 'MAY' MAY, 'JUN' JUN,
'JUL' JUL, 'AUG' AUG, 'SEP' SEP, 'OCT' OCT, 'NOV' NOV, 'DEC' DEC
)
)
order by order_year;
Source: oracle.com
Posts
0 comments:
Post a Comment