So, I want to create a word cloud to identify common and major words of my free text, which is split into a column spread over several records.

select *
from alice_in_wonderland

1 So she was considering in her own mind (as well as she could, for the hot day made her feel very sleepy and stupid), whether the pleasure of making a daisy-chain would be worth the trouble of getting up and picking the daisies, when suddenly a White Rabbit with pink eyes ran close by her.
2 There was nothing so very remarkable in that; nor did Alice think it so very much out of the way to hear the Rabbit say to itself, “Oh dear! Oh dear! I shall be late!” (when she thought it over afterwards, it occurred to her that she ought to have wondered at this, but at the time it all seemed quite natural); but when the Rabbit actually took a watch out of its waistcoat-pocket, and looked at it, and then hurried on, Alice started to her feet, for it flashed across her mind that she had never before seen a rabbit with either a waistcoat-pocket, or a watch to take out of it, and burning with curiosity, she ran across the field after it, and fortunately was just in time to see it pop down a large rabbit-hole under the hedge.
3 In another moment down went Alice after it, never once considering how in the world she was to get out again.
4 The rabbit-hole went straight on like a tunnel for some way, and then dipped suddenly down, so suddenly that Alice had not a moment to think about stopping herself before she found herself falling down a very deep well.
...

Can I create a new table with this text split into words and have one record per word?

Luckily, Lalit Kumar explains exactly how to do this here.

Step #1

• So, actually, a single whitespace “ “ can be used as the delimiter.
• We can use Oracle’s REGEX along with CONNECT BY (Hierarchical Queries) to get the desired effect.

SELECT
    TRIM(regexp_substr(t.text, '[^ ]+', 1, lines.column_value)) text
FROM
    alice_in_wonderland        t,
    TABLE ( CAST(MULTISET(
        SELECT
            level
        FROM
            dual
        CONNECT BY
            regexp_substr(t.text, '[^ ]+', 1, level) IS NOT NULL
    ) AS sys.odcinumberlist) ) lines
ORDER BY
    lines.column_value


TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
and
dry
leaves,
and
the
fall
was
over.

...

Step #2

Now we can add GROUP BY and COUNT statements using wrapper SQL statement.

SELECT
    text,
    COUNT(1) word_count
FROM
    (
        SELECT
            TRIM(regexp_substr(t.text, '[^ ]+', 1, lines.column_value)) text
        FROM
            alice_in_wonderland        t,
            TABLE ( CAST(MULTISET(
                SELECT
                    level
                FROM
                    dual
                CONNECT BY
                    regexp_substr(t.text, '[^ ]+', 1, level) IS NOT NULL
            ) AS sys.odcinumberlist) ) lines
        ORDER BY
            lines.column_value
    ) x
GROUP BY
    text
ORDER BY
    2 DESC


the 61
to 48
she 47
and 41
was 40
a 39
it 31
of 30
I 18
in 18
very 17
that 16
...

Note the space used in the REGEXP satement: regexp_substr(t.text, ‘[^😊]+’, 1, level)

We got this, Let’s go!

🌬️Oracle SQL — Split Table Column into words as Records was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

I got an IO error in my application. Trying to figure out what had happened, I’ve noticed Linux shell commands are generating a “No Space Left on Device” error message. This means once again, my Linux server ran out of disk space.

Finding out what is consuming disk space is not an easy task and at this point, we cannot install any 3rd party utilities because there is no space left on the device.

Luckily, we can use the following methods.

Method #1 — Using du Command

We need to identify the specific directories consuming a large amount of disk space. For this, a directory traversing is required.

du -ch --max-depth=2 .

Output example:

.0K    ./bin
0       ./public/images
0       ./public/javascripts
4.0K    ./public/stylesheets
4.0K    ./public
12K     ./views
8.0K    ./routes
8.0K    ./services
60K     .
60K     total

— max-depth=2 is the traversing subdirectory depth.

Method #2 — Using the Tree command

I am using Oracle Enterprise Linux which has the Tree command preinstalled.

tree --du -h

Output example:

.
├── [ 1.8K]  app.js
├── [ 1.6K]  bin
│   └── [ 1.5K]  www
├── [  392]  package.json
├── [  204]  public
│   ├── [    6]  images
│   ├── [    6]  javascripts
│   └── [  134]  stylesheets
│       └── [  111]  style.css
├── [ 2.0K]  routes
│   ├── [ 1.8K]  index.js
│   └── [  203]  users.js
├── [ 4.9K]  services
│   ├── [ 2.5K]  MqttAqBridge.js
│   └── [ 2.4K]  QueueService.js
└── [  336]  views
    ├── [   84]  error.jade
    ├── [   66]  index.jade
    └── [  125]  layout.jade

11K used in 8 directories, 11 files

Method #3 — Using my own script

Writing our own script would be slower and less efficient as we need to recursively traverse the directory tree, however, we can design the printed output as we see fit.

#!/bin/bash

dir_recurse() {
    cd $1
 
 # Calculate depth
 depth=`pwd | tr -d -c '/' | wc -m`
    
 # Add spaces for tree print
 empty_space=""
 for i in `seq 1 $depth` ; do
  empty_space=$empty_space' '
 done
 
 # Calculate disk space used
 x=`du -skh`
 echo "${empty_space} ${1} ${x}"
 
 # Traverse directories
    for i in *;do
        if [ -d "$i" ];then
            # recurse
   dir_recurse "$i" 
        fi
    done
 cd ..
}

dir_recurse .

Usage example:

./mytree.sh

Output example:

. 60K       .
     bin 4.0K   .
     public 4.0K        .
      images 0  .
      javascripts 0     .
      stylesheets 4.0K  .
     routes 8.0K        .
     services 8.0K      .
     views 12K  .

Filtering the output

So, three different methods generated a similar output showing the unit of measure e.g. K for Kilobytes, M for Megabytes, or G for Gigabytes. As a typical server holds millions of files, the output will most likely contain lots of small directories we can filter out by using the Linux redirection operator:

| grep G

For example:

tree --du -h | grep G]

This will show the directories with Gigabytes of files (or subdirectories).

There are more methods to check disk space usage. If you know some, feel free to drop a comment and share your thoughts.

🤝🏼

🐧 Linux — Check Disk Space Usage was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

I have an Oracle database table with 101 Monday Motivation Quotes and I would like my app to randomly pick and display a quote on the homepage.

What is the easiest way to select a random record from an Oracle database table?

The following will result in one random record:

SELECT q.text FROM quotes q ORDER BY DBMS_RANDOM.value FETCH NEXT 1 ROWS ONLY;

TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-----
"Start where you are. Use what you have. Do what you can." - Arthur Ashe

The quotes can be found on the positivity blog

“A ship is always safe at shore but that is not what it’s built for.”
– Albert Einstein

🍕

🦆 Oracle — Query Random Record was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

Virtual Network Computing (VNC) is a graphical desktop sharing system that uses the Remote Frame Buffer protocol (RFB) to remotely control another computer. It transmits the keyboard and mouse input from one computer to another, relaying the graphical-screen updates, over a network.

Which VNC viewer/client can I use on macOS?

There are several VNC clients for macOS. Most of which are not free. However, TigerVNC is free and can be downloaded via their page on GitHub. The current version can be downloaded from here.

TigerVNC was originally based on the (never-released) VNC 4 branch of TightVNC. More information regarding the motivation for creating this project can be found in the project announcement.

Important note: the version which is installed via homebrew E.g. “brew install tiger-vnc” did not work well for me. So, I highly recommend installing manually and not via homebrew

🖼 VNC Viewer for macOS was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

Oracle has a ROWNUM Pseudocolumn. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Does MongoDB have something like Oracle SQL ROWNUM?

As of MongoDB version 5.0, there is!

Definition
$setWindowFields
New in version 5.0.

Performs operations on a specified span of documents in a collection, known as a window, and returns the results based on the chosen window operator.

Definition
New in version 5.0.

$documentNumber
Returns the position of a document (known as the document number) in the $setWindowFields stage partition.

Example

I have a dataset which is a list of the top largest articles (pages) in Wikipedia :

{
  "Article": "2022 United States House of Representatives elections",
  "Size": 534174
}

{
  "Article": "COVID-19 pandemic in New South Wales",
  "Size": 526730
}

{
  "Article": "Ricky Martin",
  "Size": 470596
}

...

I would like to add a “ranking” by size field to each document that represents an index from the smallest to the largest.

For example, in the documents above:

• Rank #1 — 2022 United States House of Representatives elections with a size of 534174
• Rank #2 — COVID-19 pandemic in New South Wales with a size of 526730
• Rank #3 — Ricky Martin with a size of 470596

Rank #1 is the largest article and Rank #3 is the smallest in this example.

In Oracle SQL we would simply use ORDER BY Size and add the pseudo column ROWNUM

In MongoDB we can use an aggregation pipeline with $setWindowFields and $documentNumber in the following manner:

[{$setWindowFields: {
 sortBy: {
  Size: -1
 },
 output: {
  rank: {
   $documentNumber: {}
  }
 }
}}]

• The documents are sorted by size in descending order
• a new field named “rank” is added to each document with the index value in the sorted document list

So now the collection looks like this:

{
  "Article": "COVID-19 pandemic in New South Wales",
  "Size": 526730,
  "rank": 2
}

{
  "Article": "List of Falcon 9 and Falcon Heavy launches",
  "Size": 502423,
  "rank": 4
}

{
  "Article": "Presidency of Donald Trump",
  "Size": 491556,
  "rank": 5
}

...

This is a very useful feature that was added to MongoDB 5.0 and I’m utilizing it a lot when building charts in MongoDB Charts.

⚘

🚣‍♀️ MongoDB — SQL ROWNUM pseudo column was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

Friends aired between 1994–2004, Won 6 Primetime Emmys. However, one question remains unanswered …

Who is the main character?

Is it Joey, Rachel, Ross, Phoebe, Monica, or Chandler?

Using Quantitative Analysis, I tried finding an answer.

• Method #1 — Whoever has the most mentions in the episode title is the main character
• Method #2 — whoever has the most mentions in the episode summary is the main character

You can view the complete analysis here.

Method #1

Each Friends episode is usually titled either “The One Where” or “The One With,” followed by a character name.

Here are some examples:

• Season 2 Episode 16: “The One Where Joey Moves Out”
• Season 3 Episode 15: “The One Where Ross and Rachel Take a Break”

Therefore, the character who appears the most in an Episode’s title is the main character.

Using this dataset from Kaggle, we can utilize the following information:

• Year of prod: Year of production
• Season: Season of the TV Show (1 to 10)
• Episode Title: The title of the episode
• Duration: The duration of the episode
• Summary: A summary of the episode
• Director: The director of each episode
• Stars: Ranking on IMDB
• Votes: How many people voted on IMDB

So I’ve loaded this dataset into MongoDB and used the following aggregation pipeline to count each character mentioned in the episode title. For example, Monica:

[{$match: {
 Episode_Title: {
  $regex: 'Monica'
 }
}}, {$group: {
 _id: 1,
 myCount: {
  $sum: 1
 }
}}, {$addFields: {
 name: 'Monica'
}}]

Which produced the following result:

_id: 1
myCount: 9
name: “Monica”

Monica was mentioned only in 9 episodes. Then I moved on to the rest of the characters creating a union of all results.

And so it seems Rachel Greene is the main character!

Followed by Ross, Joey, Chandler, Phoebe, and Monica.

Method #2

Each episode has a plot. The plot can be summarized.

Here are some examples:

• Season 1 Episode 19: “The gang frantically search for Ross’s monkey Marcel after Rachel loses him.”
• Season 8 Episode 14: “Chandler’s very curious about a locked closet, but Monica won’t tell him what is inside.”

Therefore, the character who appears the most in an Episode’s summary is the main character.

So I’ve utilized the same dataset loaded into MongoDB and revised the aggregation pipeline referencing the summary field:

[{$match: {
 Summary: {
  $regex: 'Monica'
 }
}}, {$group: {
 _id: 1,
 myCount: {
  $sum: 1
 }
}}, {$addFields: {
 name: 'Monica'
}}]

Which produced the following result:

_id: 1
myCount: 152
name: “Monica”

Monica was one of the main characters in 152 episodes. Then I moved on to the rest of the characters creating a union of all results.

And so it seems Rachel Greene again is the main character!

Followed by Monica, Chandler, Ross, Joey, and Phoebe.

Method #3

What if a single method is not enough to determine who is the main character?

We can combine Method #1 + #2 to have a new type of score.

As it happens, Rachel is the winner in each Method, so it is safe to determine.

🥇Rachel Greene is the winner and the main character in Freinds!🥇

Bonus: “The One Where” VS. “The One With”

There were 163 episodes of “The One With” and 54 episodes of “The One Where”.

Final Thoughts

The full analysis can be viewed here.

Who is the most favorite character in Friends? 🤔

(mine is Ross)

Who is the Main Character on Friends? was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

In set theory, the union (denoted by ∪) of a collection of sets is the set of all elements in the collection.

In SQL the UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL is used.

Can we do a UNION in MongoDB?

We sure can! as of Version 4.4, we have $unionWith.

Let’s take a closer look using this friends dataset:

{
 “Year_of_prod”: 1995,
 “Season”: 2,
 “Episode Number”: 9,
 “Episode_Title”: “The One with Phoebe’s Dad”,
 “Duration”: 22,
 “Summary”: “Phoebe tracks down her father, but isn’t sure whether she should meet him. Ross asks Rachel to make a list of pros and cons about him.”,
 “Director”: “Kevin Bright”,
 “Stars”: 8,
 “Votes”: 3444
}

• This episodes collection has a document per episode in Friends
• Each document has an Episode_Title field which is usually something like: “The one where…” or “The one with…”

The One Where VS. The One With

Now, Let’s query how many episodes there are of each type.

The following aggregation pipeline counts how many episodes had the title “The One Where” using $regex and $group:

[{$match: {
 Episode_Title: {
  $regex: 'The One Where'
 }
}}, {$group: {
 _id: 'where',
 count: {
  $sum: 1
 }
}}]

The result:

_id: “where”
count: 54

Now, we can use $unionWith

New in version 4.4.

Performs a union of two collections; i.e. $unionWith combines pipeline results from two collections into a single result set. The stage outputs the combined result set (including duplicates) to the next stage.

I’ll duplicate the previous pipeline and change the strings to “The One with” accordingly:

[{$match: {
 Episode_Title: {
  $regex: 'The One with'
 }
}}, {$group: {
 _id: 'with',
 count: {
  $sum: 1
 }
}}]

Finally, we’ll paste into the $unionWith step in the pipeline statement:

[{$match: {
 Episode_Title: {
  $regex: 'The One Where'
 }
}}, {$group: {
 _id: 'where',
 count: {
  $sum: 1
 }
}}, {$unionWith: {
 coll: 'episodes',
 pipeline: [
  {
   $match: {
    Episode_Title: {
     $regex: 'The One with'
    }
   }
  },
  {
   $group: {
    _id: 'with',
    count: {
     $sum: 1
    }
   }
  }
 ]
}}]

The result is a union of both queries:

_id: “where”
count: 54

_id: “with”
count: 163

🍿

MongoDB — Unions 🇬🇧 was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

Optimism bias is a tendency to overestimate the likelihood of positive events occurring in the future and underestimate the likelihood of negative life events. Such biased expectations are generated and maintained in the face of counter-evidence through a tendency to discount undesirable information. An optimism bias can alter risk perception and decision-making in many domains, ranging from finance to health.

Optimism bias can be demonstrated using the Rocky movie franchise. In Rocky 3, Rocky’s decision to fight led to a series of life-altering events ranging from finance to health.

Let’s recap the movies starting Rocky 1 all the way up to Rocky 5 and examine what had actually happened in comparison to what could have happened if only Rocky had decided not to fight.

(In case you haven’t watched the movies, it is recommended to watch them before continuing to read this)

ROCKY I

• Rocky is poor
• Rocky falls in love with Adrian (Talia Shire)
• Rocky fights Apollo (Carl Weathers) for the heavyweight championship

ROCKY II

• Rocky is still poor
• Rocky marries Adrian
• Rocky wins against Apollo for the heavyweight championship

Now, this is where we split into two timelines as Rocky’s life takes a turn in Rocky 3.

What Actually Happened

ROCKY III

• Rocky is now rich and doesn’t need to fight for a living
• Rocky once publicly provoked, decides to fight against Clubber Lang (Mr. T) after winning all his fights during the last 5 years
• A few minutes before the fight, his beloved trainer (coach) Mik (Burgess Meredith) collapses and dies
• Rocky loses the championship belt to Clubber Lang
• Rocky’s past opponent Apollo Creed decides to encourage and train Rocky for a rematch (eye of the tiger). They become friends
• Rocky beats Clubber Lang in a rematch and regain the championship title

All well ends well? not exactly. Mik’s premature death could have been avoided.

ROCKY IV

• Apollo decides to fight against a Russian champion named Drago (Dolph Lundgren) and asks Rocky to become his trainer
• Apollo dies during an exhibition fight against Drago
• Rocky fly to Russia to train before his fight against Drago
• Rocky beats Drago (everybody can change)

All well ends well? not exactly. Apollo’s premature death could have been avoided.

ROCKY V

• Rocky is back in the USA only to discover his brother-in-law Paulie (Burt Young) lost all of Rocky’s money
• Rocky is now poor
• Due to his last match against Drago, Rocky now has neurological damage
• Rocky is living in a bad neighborhood and his son is being bullied and having a hard time in school
• Rocky takes a young and talented fighter under his wing training him for the heavyweight title
• Rocky betrayed by his protege, ends up winning a street fight against him (only in America)

All well ends well? not exactly. Rocky is poor and his family is having a hard time.

What Could Have Happened

If only Rocky had stepped down in Rocky 3 and enjoyed a quiet retirement the following could have been avoided:

1. Mik would have lived longer (instead of dying a few minutes before the fight)
2. Apollo wouldn’t have died (Rocky would have never become friends with Apollo and as a result wouldn’t have encouraged Apollo)
3. Rocky wouldn’t have lost all his money (Rocky would have gone to Russia and left Paulie in charge only to lose all of his money)
4. Rocky wouldn’t have neurological damage (Rocky would never fight Drago to avenge Apollo’s death)
5. Rocky’s family wouldn’t have had a hard time being poor (Rocky would never become poor)

But hey! we wouldn’t have 3 additional Rocky movies.

Final Thoughts

“Let me tell you something you already know. The world ain’t all sunshine and rainbows. It’s a very mean and nasty place and I don’t care how tough you are it will beat you to your knees and keep you there permanently if you let it. You, me, or nobody is gonna hit as hard as life. But it ain’t about how hard ya hit. It’s about how hard you can get hit and keep moving forward. How much you can take and keep moving forward. That’s how winning is done! Now if you know what you’re worth then go out and get what you’re worth. But ya gotta be willing to take the hits, and not pointing fingers saying you ain’t where you wanna be because of him, or her, or anybody! Cowards do that and that ain’t you! You’re better than that! I’m always gonna love you no matter what. No matter what happens. You’re my son and you’re my blood. You’re the best thing in my life. But until you start believing in yourself, ya ain’t gonna have a life.“

(Rocky Balboa 2006)

Optimism Bias Illustration with Rocky Movie Franchise 🥊 was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

68 companies have gone public during 2021. 6 of which were Direct Listing. 9 were SPAC and 53 were IPO.

A direct public offering (DPO) or direct listing is a method by which a company can offer an investment opportunity directly to the public.

A special purpose acquisition company (SPAC), is a shell corporation listed on a stock exchange with the purpose of acquiring a private company, thus making it public without going through the traditional initial public offering process.

An initial public offering (IPO) or stock launch is a public offering in which shares of a company are sold to institutional investors and usually also retail (individual) investors.

Inspired by this visualization and this article, I tried to find out whether the method used for “going public” had any long-run effect on the stock price. By building this dashboard, I was able to perform some analysis.

By comparing the initial public offering price to today’s January 9th, 2022 price and checking if the stock went up or down, we can see that:

1. 28% of companies gone public using IPO went up, while 71% went down.

2. 100% of companies gone public using SPAC went down.

3. 50% of companies gone public using Direct Listing went up, while 50% went down.

We can also check the average stock performance per “Listing Type” and see that most stocks went down more than up.

We can see that SPAC was used only during the first half of 2021.

Final Thoughts

SPAC? hmm.

The Dashboard can be viewed here. Any feedback is welcomed and appreciated 🙏🏻

I’ve also published the dataset used in Kaggle.

📈Companies Gone Public in 2021 was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.

If you are working with Linux you are probably using the nohup command.

nohup is a POSIX command which means “no hang-up”. Its purpose is to execute a command such that it ignores the HUP (hangup) signal and therefore does not stop when the user logs out.

nohup is how I am able to run commands/apps/jobs in a terminal session in the background so when I am closing the terminal session, the process remains running in the background.

However, for some reason, this is not working for me on macOS.

For example, I wrote the following script:

1. Kill Microsoft Teams process
2. Delete Microsoft Teams local cache directory
3. Relaunch Microsoft Teams app

ps -ef | grep teams | cut -d " " -f2 | xargs kill -9

sleep 3

rm -rf "/Users/me/Library/Application Support/Microsoft/Teams"

nohup "/Applications/Microsoft Teams.app/Contents/MacOS/Teams" &

But each time this script finished executing and the terminal window is closed, the Microsoft Teams app is terminated as well.

Using the screen command instead of nohup didn’t work as well and the only workaround I’ve found for this is using the open command:

ps -ef | grep teams | cut -d " " -f2 | xargs kill -9

sleep 3

rm -rf "/Users/me/Library/Application Support/Microsoft/Teams"

open -a "/Applications/Microsoft Teams.app/Contents/MacOS/Teams"

Now the script finished executing yet the Teams app is still running in the background.

🐈‍⬛

🍎 Why isn’t “nohup” working on macOS? was originally published in idomongo on Medium, where people are continuing the conversation by highlighting and responding to this story.