Select most occurrences of a varchar2 in Oracle

group byoracle

We need to group data in an Oracle database and must get a ProductNumber and a Description. The Description needs to be the one with the most occurrences inside the table. Both are a varchar2. See below for the raw data:

ProductNumber	Description
ABC	Product ABC (with discount)
ABC	Product ABC
ABC	Product ABC
DEF	Product DEF

Expected result:

ProductNumber	Description
ABC	Product ABC
DEF	Product DEF

We've tried this but don't find a way to include the Description with most occurences.

select distinct cnt1.ProductNumber
from (select COUNT(*) as total, ProductNumber
      from Inventory
      group by ProductNumber) cnt1,
     (select MAX(total) as maxtotal, ProductNumber
      from (select COUNT(*) as total, ProductNumber 
            from Inventory 
            group by ProductNumber)
      group by ProductNumber) cnt2
where cnt1.total = cnt2.maxtotal;

Here you could find a fiddle.

Do you know how we could do it?

Best Answer

Here is one way to do it using window functions:

select ProductNumber, Description
from (
  select ProductNumber, Description
       , row_number() over (partition by productnumber order by cnt1, cnt2 desc) as rn
  from (
    select ProductNumber, Description
         , count(1) over (partition by ProductNumber) as cnt1
         , count(1) over (partition by ProductNumber, Description) as cnt2
    from inventory
  ) t
) u 
where rn = 1;

EDIT: Added plans for a slightly bigger set of data, Fiddle

Verace query

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |    676 |00:00:00.01 |     122 |       |       |          |
|   1 |  SORT UNIQUE               |           |      1 |  17576 |    676 |00:00:00.01 |     122 | 46080 | 46080 |40960  (0)|
|*  2 |   HASH JOIN SEMI           |           |      1 |  17576 |  17576 |00:00:00.01 |     122 |  2178K|  2050K| 2666K (0)|
|   3 |    TABLE ACCESS FULL       | INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
|*  4 |    VIEW                    |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|*  5 |     WINDOW SORT PUSHED RANK|           |      1 |  17576 |    676 |00:00:00.01 |      61 | 48128 | 48128 |43008  (0)|
|   6 |      HASH GROUP BY         |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1214K|  1214K| 1854K (0)|
|   7 |       TABLE ACCESS FULL    | INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T1"."DESCRIPTION"="I2"."DESCRIPTION")
   4 - filter("T1"."RN"=1)
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY "I1"."PRODUCTNUMBER" ORDER BY COUNT(*) DESC )<=1)

H. Pauwelyn's modified query

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |    676 |00:00:00.01 |     122 |       |       |          |
|   1 |  HASH UNIQUE          |           |      1 |    176 |    676 |00:00:00.01 |     122 |  2170K|  2170K| 1427K (0)|
|*  2 |   HASH JOIN RIGHT SEMI|           |      1 |    176 |    676 |00:00:00.01 |     122 |  2546K|  2546K|  654K (0)|
|   3 |    VIEW               |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|   4 |     HASH GROUP BY     |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1520K|  1520K| 1882K (0)|
|   5 |      TABLE ACCESS FULL| INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
|   6 |    VIEW               |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|   7 |     HASH GROUP BY     |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1520K|  1520K| 1882K (0)|
|   8 |      TABLE ACCESS FULL| INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CNT1"."TOTAL"="CNT2"."MAXTOTAL")

Lennart's query

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |    676 |00:00:00.03 |     208 |       |       |          |
|*  1 |  VIEW                    |      |      1 |  17576 |    676 |00:00:00.03 |     208 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  17576 |    676 |00:00:00.03 |     208 |  1116K|   557K|  991K (0)|
|   3 |    VIEW                  |      |      1 |  17576 |  17576 |00:00:00.02 |     208 |       |       |          |
|   4 |     WINDOW BUFFER        |      |      1 |  17576 |  17576 |00:00:00.02 |     208 |  1116K|   557K|  991K (0)|
|   5 |      INDEX FULL SCAN     | IX1  |      1 |  17576 |  17576 |00:00:00.01 |     208 |       |       |          |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PRODUCTNUMBER" ORDER BY "CNT1",INTERNAL_FUNCTION("CNT2") DESC 
              )<=1)

Related Solutions

Oracle 11g – Performance Improvements for Inserts

Just saw the update, 60-col table with mostly VARCHAR(2k) fields -- that is (potentially) a monster table.

First things first...

You have to understand your bottleneck FIRST. On the app side, go all the way back to your single-threaded batch-insert solution (1/2/3k at a time) and begin running it and login to the DB machine and run a 'top' -- see how much time the DB process is taking AND how much (if any) wa% time the machine is showing.

If top is showing you ANY wa% time, that means your DB is I/O bound and you likely need to consider multiple DB machines (shards) or consider throwing SSDs on the host machine.

That's it; your research stops here. It doesn't matter how much CPU the DB was taking or how saturated your app client was; if you are hitting I/O latency issues on the host DB, that is as fast as it will EVER go for you.

TIP If hardware changes are out of the question, depending on the filesystem you are running (Linux) you can try disabling logging or metadata writing for the DB to slightly improve performance at the filesystem level. You can do something similar on NTFS, but this will only give you a little boost. This won't be 2x.

Now, second things second...

Let's say you had next to no wa% time but your CPU is pegged fully by the DB process. Your only option now is to introduce more DB machines (shards) and split the work.

Again, you're done with your research if this is the case. Nothing you can do to tweak the CPU to go faster.

Lastly, third things... third...

Let's say the DB isn't doing much of anything. Then, go to the client machine running the batch insert and check the CPU load -- is it pegged? If so, fire up some more machines doing the exact same batch inserts and see if you can get a linear ramp.

If the CPU isn't pegged, fire up some more threads on the same machine until it is pegged and see how the DB scales.

I think you may have already tried that, so my guess is that either your client host was already pegged (and more threads isn't going to make a difference) or the DB was already hitting its limit and can't scale any farther.

Addendum

Doing raw inserts on an unindexed table that has no garbage in it is essentially an APPEND operation which should be going as fast as the disk can handle the writes.

Creating more tables on the same host machine isn't going to help, if anything it will increase your disk seeks (to get to the other tables on disk to append to) and will slow things down.

It is critical to figure out that bottleneck first, then we can optimize the hell out of it.

Hope that helps! Keep us posted.

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Best Answer

Related Solutions

Oracle 11g – Performance Improvements for Inserts

Mysql – Pivot table with MySQL

Related Question