Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--PostgreSQL 9.6
--'\\' is a delimiter

create table charfreq (
    c text,
    f float,
    lang text
    );
insert into charfreq values
 ('а',  8.04,  'ru'),
 ('б',  1.55,  'ru'),
 ('в',  4.75,  'ru'),
 ('г',  1.88,  'ru'),
 ('д',  2.95,  'ru'),
 ('е',  8.21,  'ru'),
 ('ё',  0.22,  'ru'),
 ('ж',  0.8,   'ru'),
 ('з',  1.61,  'ru'),
 ('и',  7.98,  'ru'),
 ('й',  1.36,  'ru'),
 ('к',  3.49,  'ru'),
 ('л',  4.32,  'ru'),
 ('м',  3.11,  'ru'),
 ('н',  6.72,  'ru'),
 ('о', 10.61,  'ru'),
 ('п',  2.82,  'ru'),
 ('р',  5.38,  'ru'),
 ('с',  5.71,  'ru'),
 ('т',  5.83,  'ru'),
 ('у',  2.28,  'ru'),
 ('ф',  0.41,  'ru'),
 ('х',  1.02,  'ru'),
 ('ц',  0.58,  'ru'),
 ('ч',  1.23,  'ru'),
 ('ш',  0.55,  'ru'),
 ('щ',  0.34,  'ru'),
 ('ъ',  0.03,  'ru'),
 ('ы',  1.91,  'ru'),
 ('ь',  1.39,  'ru'),
 ('э',  0.31,  'ru'),
 ('ю',  0.63,  'ru'),
 ('я',  2.0,   'ru'),

 ('a',  8.167, 'en'),
 ('b',  1.492, 'en'),
 ('c',  2.782, 'en'),
 ('d',  4.253, 'en'),
 ('e', 12.702, 'en'),
 ('f',  2.228, 'en'),
 ('g',  2.015, 'en'),
 ('h',  6.094, 'en'),
 ('i',  6.966, 'en'),
 ('j',  0.153, 'en'),
 ('k',  0.772, 'en'),
 ('l',  4.025, 'en'),
 ('m',  2.406, 'en'),
 ('n',  6.749, 'en'),
 ('o',  7.507, 'en'),
 ('p',  1.929, 'en'),
 ('q',  0.095, 'en'),
 ('r',  5.987, 'en'),
 ('s',  6.327, 'en'),
 ('t',  9.056, 'en'),
 ('u',  2.758, 'en'),
 ('v',  0.978, 'en'),
 ('w',  2.360, 'en'),
 ('x',  0.150, 'en'),
 ('y',  1.974, 'en'),
 ('z',  0.074, 'en'),
 
 ('a',  7.636, 'fr'),
 ('b',  0.901, 'fr'),
 ('c',  3.26 , 'fr'),
 ('d',  3.669, 'fr'),
 ('e', 14.715, 'fr'),
 ('f',  1.066, 'fr'),
 ('g',  0.866, 'fr'),
 ('h',  0.737, 'fr'),
 ('i',  7.529, 'fr'),
 ('j',  0.613, 'fr'),
 ('k',  0.074, 'fr'),
 ('l',  5.456, 'fr'),
 ('m',  2.968, 'fr'),
 ('n',  7.095, 'fr'),
 ('o',  5.796, 'fr'),
 ('p',  2.521, 'fr'),
 ('q',  1.362, 'fr'),
 ('r',  6.693, 'fr'),
 ('s',  7.948, 'fr'),
 ('t',  7.244, 'fr'),
 ('u',  6.311, 'fr'),
 ('v',  1.838, 'fr'),
 ('w',  0.049, 'fr'),
 ('x',  0.427, 'fr'),
 ('y',  0.128, 'fr'),
 ('z',  0.326, 'fr'),
 ('à',  0.486, 'fr'),
 ('â',  0.051, 'fr'),
 ('œ',  0.018, 'fr'),
 ('ç',  0.085, 'fr'),
 ('è',  0.271, 'fr'),
 ('é',  1.504, 'fr'),
 ('ê',  0.218, 'fr'),
 ('ë',  0.008, 'fr'),
 ('î',  0.045, 'fr'),
 ('ï',  0.005, 'fr'),
 ('ô',  0.023, 'fr'),
 ('ù',  0.058, 'fr'),
 ('û',  0.06 , 'fr');

INSERT 0 98


create table test (id int, message text);
insert into test values
 (1, 'hi'),
 (2, 'ok'),
 (3, 'துய'),
 (4, 'нет'),
 (5, 'été');

INSERT 0 5


select id, message, lang, score
from (
  select *, row_number() OVER (partition by id, message order by score desc) as rownum
  from (
    select id, message, lang, coalesce(sum(f), 0)/length(message) as score
    from (
      select *, unnest(STRING_TO_ARRAY(message, NULL)) as c from test
    ) as a
    left join charfreq b
    using (c)
    group by 1,2,3
  ) as a
) as a
where rownum = 1;


id
message
lang
score


1
hi
en
6.53

2
ok
en
4.1395

3
துய
null
0

4
нет
ru
6.92

5
été
fr
3.41733333333333



SELECT 5
id	message	lang	score
1	hi	en	6.53
2	ok	en	4.1395
3	துய	null	0
4	нет	ru	6.92
5	été	fr	3.41733333333333