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');
98 rows affected

create table test (id int, message text);
insert into test values
 (1, 'hi'),
 (2, 'ok'),
 (3, 'துய'),
 (4, 'нет'),
 (5, 'été'),
 (6, 'les aventures'),
 (7, 'the adventures');
7 rows affected

select id, message, lang, logp
from (
  select *, row_number() OVER (partition by id, message order by logp desc) as rownum
  from (
    select id, message, lang,
      coalesce(sum(log(f/100)), 0)  -- matching chars
      + (length(message) - count(b.*)) * log(0.01/100)  -- default prob for missing: 0.01%
      as logp
    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
logp


1
hi
en
-2.37211407999113

2
ok
en
-3.23691628379788

3
துய
null
-12

4
нет
ru
-3.49261901506772

5
été
fr
-4.78552588584673

6
les aventures
fr
-17.4754678487836

7
the adventures
en
-19.6102708215396
id	message	lang	logp
1	hi	en	-2.37211407999113
2	ok	en	-3.23691628379788
3	துய	null	-12
4	нет	ru	-3.49261901506772
5	été	fr	-4.78552588584673
6	les aventures	fr	-17.4754678487836
7	the adventures	en	-19.6102708215396