Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Get Difference Between Timestamps Using Pivot

User_OMEF8Aug 30 2019 — edited Sep 5 2019

Hello,

I am on Oracle 11g and I am trying to formulate a SQL query to produce results using a PIVOT (not sure if it is actually necessary).  The report that I want to create has different "Groups" of information and I was wondering if this could be achieved with a single query.  I know I can do this if I run multiple queries, then combine the information, then do some other operations to get what I need, but there are too many steps if I do it this way.

Here is what I want to do...  I have many stores and different items sold at each store.  For my purposes, I am only concerned with certain stores and certain items.  I also have a timestamp field that tells me when the item arrived and when it was sold.  I need to calculate the difference between the 2 timestamp fields (sold minus arrived, what is the difference between?)

  • Stores - varchar2
  • Items - varchar2
  • Arrived - Timestamp
  • Sold - Timestamp

Below is a sample output of what I want the report to look like.  Note that the column headers (2019-08-01, 2019-08-02, and 2019-08-03) would be dynamic (so I could have more dates across), but effectively, this would be the date difference of when the ITEM was sold.  For example, DOWNTOWN, PENCIL, 2019-08-01 ==> 5.  This is the calculated total number of days for all PENCILS sold that date.

If I had 5 PENCILS and they all had an Arrival Date of 2019-07-31 and a Sold Date of 2019-08-01.  This would equal 5 days.

If I had 3 PENCILS...

  • PENCIL 1 Arrival Date 2019-07-31 and Sold Date 2019-08-01 ==> 1 Day
  • PENCIL 2 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
  • PENCIL 3 Arrival Date 2019-07-30 and Sold Date 2019-08-01 ==> 2 Days
  • Total Days ==> 5 days

STOREITEMS
2019-08-01
2019-08-022019-08-03
DOWNTOWNPEN123
DOWNTOWNPENCIL567
DOWNTOWNERASER213
2.67 (AVERAGE DATE DIFFERENCE)3 (AVERAGE DATE DIFFERENCE)4.33 AVERAGE DATE DIFFERENCE
4.SUBURBPEN987
SUBURBPENCIL321
SUBURBERASER456
5.33 AVERAGE DATE DIFFERENCE5 AVERAGE DATE DIFFERENCE4.67 AVERAGE DATE DIFFERENCE

Here is my attempt SQL query...

select * from (

     select sn.storenamecolumn, i.itemcolumn, trunc(d.solddatecolumn)

     from itemtable i

     join storetable s on i.id = s.id

     join storenametable sn on s.storeid = sn.storename

     join itemdatetable d on i.itemid = d.id

     where i.itemcolumn in ('PEN', 'PENCIL', 'ERASER')

     and trunc(d.solddatecolumn) >= to_date('2019-08-01', 'yyyy-mm-dd') and trunc(d.solddatecolumn) < to_date('2019-08-04', 'yyyy-mm-dd')

     )

     pivot

     (

     count(*)

     for trunc(solddatecolumn) in ('2019-08-01', '2019-08-02', '2019-08-03')   <============ error missing IN keyword

     )

where storenamecolumn in ('DOWNTOWN', 'SUBURB')

order by storenamecolumn;

I keep getting the error above and I believe it has to do with the trunc() method.  If I remove the trunc() method from both the "for trunc()" and the SELECT Statement, then it will run, but the results are not right because I am only looking for a date and not the entire "timestamp".  Any suggestions on how I can achieve my desired results?  Thanks.

Also, is it possible to calculate the total average for each day and put it at the bottom of each Date column?  I edited the table above.

This post has been answered by Frank Kulash on Sep 5 2019
Jump to Answer

Comments

Mike Kutz

bitmap
Bitmap indexes implies Serialization. That's like having the db parameter _SLOW=TRUE.
It can speed up SELECTS, but at a cost for other DML operations.
In my uses, it's not about the "low cardinality" of the answers in a column, but the "low cardinality" of your query results.
Eg finding out that a particular song, sung by Adam Sandler about Tinder results for other Eskimos in his tribe, is true
I've used them to speed up ad hoc queries on final reporting tables.
Billy uses them to show you can count 42B rows in under 1s.
Partition
"Prevent FTS" is one use for Partitions.
The primary use I've seen is for data management.
Need to remove 1 mo worth of data? Drop a Partition
Also, research ILM in the Data warehouse guide. This helps automate the task of moving chunks of old data for you. (Eg move 1mo worth of 3yr old data from tablespace on SSD to compressed read-only tablespace on SATA)

User_JNHXJ

Thank for replying mike
i have a couple of question
1.what u mean by ad hoc query is query using bind variable in where clause? or is there a different meaning?(from what i know ad hoc query is a type of query where result set is depent on the value supplied to a variable)

2."Need to remove 1 mo worth of data? Drop a Partition" even if we drop a partition the actual table still have the data from that partition right? and for what purpose we dropping this data? for memory or just deleting old data?

Mike Kutz

Ad hoc
This is what most end users want to perform.
APEX IR searches are ad hoc queries. Especially those faceted searches.
Bitmap indexes could drastically improve multi-column queries of these types even on a 1 B row table.
But, INSERT/UPDATE/DELETES would be horrible. You'll want to do infrequent bulk operations from a single transaction only. Tools like DBMS_PARALLEL_EXECUTE will cause the crud operation to take longer if parallel_level is higher than 1. (I learned this the hard way)
Drop Partition
You usually remove old data for legal reasons.
Partitions are individuals tables magically joined together to act as 1 table. If you read the history of Oracle, Partitions started in v7.3 as Partitioned VIEWs.
So, when you DROP a Partition, you are actually dropping a table.
Which then brings up Partition EXCHANGE. In this case you are actually swapping one table for another. And because the DD is only swapping the pointers, this is extremely fast.
You can swap in/out a table's worth of data in the blink of an eye. (I'm ignoring INDEXES)
Again, I find Partitions more useful for data management. But queries performance shouldn't be ignored.

User_JNHXJ

thanks for the partition part i got a little insigth about it.
but still have question about bitmap
can you explain example of "faceted searches" is it like result set with low cardinality?(FYI first time hear faceted word so i do not understand)

Mike Kutz
1 - 5

Post Details

Added on Aug 30 2019
19 comments
945 views