Using Linux commands, I am trying to make a histogram of all three word sequence
ID: 3785056 • Letter: U
Question
Using Linux commands, I am trying to make a histogram of all three word sequences (trigram) in a database given. It needs to be sorted in decreasing order of occurrence, it needs to be case insensitive and punctuation needs to be ignored. There should be a column where it counts the number of times the trigram occurred, a column where it calculates the percentage each was used and a column that keeps a running sum of the percentages in the percent column.
The output of your command line should be:
Trigram
Frequency
No.
Percentage
Cumulative
see jane run
3
37.5000%
37.5000%
jane run see
2
25.0000%
62.5000%
run see john
1
12.5000%
75.0000%
see john run
1
12.5000%
87.5000%
run see jane
1
12.5000%
100.0000%
Trigram
Frequency
No.
Percentage
Cumulative
see jane run
3
37.5000%
37.5000%
jane run see
2
25.0000%
62.5000%
run see john
1
12.5000%
75.0000%
see john run
1
12.5000%
87.5000%
run see jane
1
12.5000%
100.0000%
Explanation / Answer
create database if not exist histogram;
use histogram;
create table trigram_table (trigram varchar(50),frequency_no int,frequency_perc double,cumalitive_perc double);
if(select count(*) from trigram_table where trigram="see jane run" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="see jane run")+1, frequency_perc =( (select (count*) from trigram_table where trigram="see jane run")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("see jane run",1,((select (count*) from trigram_table where trigram="see jane run")/(select sum(frequency_no) from trigram_table) )*100, 0));
if(select count(*) from trigram_table where trigram="see jane run" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="see jane run")+1, frequency_perc =( (select (count*) from trigram_table where trigram="see jane run")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("see jane run",1,((select (count*) from trigram_table where trigram="see jane run")/(select sum(frequency_no) from trigram_table) )*100, 0));
if(select count(*) from trigram_table where trigram="see jane run" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="see jane run")+1, frequency_perc =( (select (count*) from trigram_table where trigram="see jane run")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("see jane run",1,((select (count*) from trigram_table where trigram="see jane run")/(select sum(frequency_no) from trigram_table) )*100, 0));
if(select count(*) from trigram_table where trigram="jane run see" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="jane run see")+1, frequency_perc =( (select (count*) from trigram_table where trigram="jane run see")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("jane run see",1,((select (count*) from trigram_table where trigram="jane run see")/(select sum(frequency_no) from trigram_table) )*100, 0))
if(select count(*) from trigram_table where trigram="jane run see" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="jane run see")+1, frequency_perc =( (select (count*) from trigram_table where trigram="jane run see")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("jane run see",1,((select (count*) from trigram_table where trigram="jane run see")/(select sum(frequency_no) from trigram_table) )*100, 0))
if(select count(*) from trigram_table where trigram="run see john" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="run see john")+1, frequency_perc =( (select (count*) from trigram_table where trigram="run see john")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("run see john",1,((select (count*) from trigram_table where trigram="run see john")/(select sum(frequency_no) from trigram_table) )*100, 0));
if(select count(*) from trigram_table where trigram="see john run" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="see john run")+1, frequency_perc =( (select (count*) from trigram_table where trigram="see john run")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("see john run",1,((select (count*) from trigram_table where trigram="see john run")/(select sum(frequency_no) from trigram_table) )*100, 0));
if(select count(*) from trigram_table where trigram="run see jane" > 0 , update table trigram_table set frequency_no=(select count(*) from trigram_table where trigram ="run see jane")+1, frequency_perc =( (select (count*) from trigram_table where trigram="run see jane")/(select sum(frequency_no) from trigram_table ) )*100, insert into trigram_table("run see jane",1,((select (count*) from trigram_table where trigram="run see jane")/(select sum(frequency_no) from trigram_table) )*100, 0));
set @csum := 0;
update trigram_table
set cumalitive_perc = (@csum := @csum + frequency_perc)
order by frequency_no;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.