Re: Counting the number of repeated phrases in a column

2022-02-01 Thread Karsten Hilbert
Am Tue, Feb 01, 2022 at 11:29:50PM + schrieb Shaozhong SHI: > How about knock unique words into discrete joint up strings? Then check > whether there is any repeated words? Does it work when you try ? Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Re: Counting the number of repeated phrases in a column

2022-02-01 Thread Shaozhong SHI
On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI wrote: > There is a short of a function in the standard Postgres to do the > following: > > It is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. > > For instance: > > A cell of

Re: Counting the number of repeated phrases in a column

2022-02-01 Thread Shaozhong SHI
On Thursday, 27 January 2022, Merlin Moncure wrote: > On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure wrote: > > > > with s as (select 'Hello World Hello World' as sentence) > > select > > phrase, > > array_upper(string_to_array((select sentence from s), phrase), 1) - > > 1 as occurrances > >

Re: Counting the number of repeated phrases in a column

2022-01-27 Thread Merlin Moncure
On Thu, Jan 27, 2022 at 11:56 AM wrote: > Le 27/01/2022 à 18:35, Merlin Moncure a écrit : > > select distinct array_to_string(v[a:b], ' ') phrase, count(*) as occurrences > > from > > ( > >select array_agg(t) v > >from > >( > > select trim(replace(unnest(v), E'\n', '')) t > >

Re: Counting the number of repeated phrases in a column

2022-01-27 Thread benj . dev
Le 27/01/2022 à 18:35, Merlin Moncure a écrit : On Thu, Jan 27, 2022 at 11:09 AM Rob Sargent wrote: On 1/27/22 10:03, Merlin Moncure wrote: On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure wrote: with s as (select 'Hello World Hello World' as sentence) select phrase, array_upper(strin

Re: Counting the number of repeated phrases in a column

2022-01-27 Thread Merlin Moncure
On Thu, Jan 27, 2022 at 11:09 AM Rob Sargent wrote: > > On 1/27/22 10:03, Merlin Moncure wrote: > > On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure wrote: > > with s as (select 'Hello World Hello World' as sentence) > select > phrase, > array_upper(string_to_array((select sentence from s), phr

Re: Counting the number of repeated phrases in a column

2022-01-27 Thread Rob Sargent
On 1/27/22 10:03, Merlin Moncure wrote: On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure wrote: with s as (select 'Hello World Hello World' as sentence) select phrase, array_upper(string_to_array((select sentence from s), phrase), 1) - 1 as occurrances from ( select array_to_string(x, '

Re: Counting the number of repeated phrases in a column

2022-01-27 Thread Merlin Moncure
On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure wrote: > > with s as (select 'Hello World Hello World' as sentence) > select > phrase, > array_upper(string_to_array((select sentence from s), phrase), 1) - > 1 as occurrances > from > ( > select array_to_string(x, ' ') as phrase > from > (

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread Merlin Moncure
On Tue, Jan 25, 2022 at 11:10 AM Shaozhong SHI wrote: > > There is a short of a function in the standard Postgres to do the following: > > It is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. > > For instance: > > A cell

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread Karsten Hilbert
Am Wed, Jan 26, 2022 at 08:35:06PM + schrieb Shaozhong SHI: > Whatever. Can we try to build a regex for 'The City of London London > Great London UK ' ? Would you be so kind as do be more specific about that "we" ? Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread Rob Sargent
On 1/26/22 13:35, Shaozhong SHI wrote: On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI wrote: There is a short of a function in the standard Postgres to do the following: It is easy to count the number of occurrence of words, but it is rather difficult to count the number of oc

Counting the number of repeated phrases in a column

2022-01-26 Thread David G. Johnston
On Wednesday, January 26, 2022, Shaozhong SHI wrote: > > > Whatever. Can we try to build a regex for 'The City of London London > Great London UK ' ? > Not even if you paid me. I’d probably die before I succeeded if you tortured me. David J.

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread Shaozhong SHI
On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI wrote: > There is a short of a function in the standard Postgres to do the > following: > > It is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. > > For instance: > > A cell of

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread Ivan E. Panchenko
On 26.01.2022 11:11, Shaozhong SHI wrote: On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko mailto:i.panche...@postgrespro.ru>> wrote: On 26.01.2022 00:21, benj@laposte.net wrote: > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : >> There is a sh

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread benj . dev
>On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko > >wrote: > > >> >> On 26.01.2022 00:21, benj(dot)dev(at)laposte(dot)net wrote: >> > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : >> >> There is a short of a function in the standard Postgres to do the >> >> following: >> >> >> >> It is easy to count

Re: Counting the number of repeated phrases in a column

2022-01-26 Thread Shaozhong SHI
On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko wrote: > > On 26.01.2022 00:21, benj@laposte.net wrote: > > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : > >> There is a short of a function in the standard Postgres to do the > >> following: > >> > >> It is easy to count the number of occurrence

Re: Counting the number of repeated phrases in a column

2022-01-25 Thread Jian He
you may be interested > https://dba.stackexchange.com/q/166762/238839 On Wed, Jan 26, 2022 at 3:03 AM Ivan Panchenko wrote: > > On 26.01.2022 00:21, benj@laposte.net wrote: > > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : > >> There is a short of a function in the standard Postgres to do t

Re: Counting the number of repeated phrases in a column

2022-01-25 Thread Ivan Panchenko
On 26.01.2022 00:21, benj@laposte.net wrote: Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : There is a short of a function in the standard Postgres to do the following: It is easy to count the number of occurrence of words, but it is rather difficult to count the number of occurrence of

Re: Counting the number of repeated phrases in a column

2022-01-25 Thread benj . dev
Le 25/01/2022 à 18:10, Shaozhong SHI a écrit : There is a short of a function in the standard Postgres to do the following: It is easy to count the number of occurrence of words, but it is rather difficult to count the number of occurrence of phrases. For instance: A cell of value:  'Hello W

Re: Counting the number of repeated phrases in a column

2022-01-25 Thread Laura Smith
‐‐‐ Original Message ‐‐‐ On Tuesday, January 25th, 2022 at 17:10, Shaozhong SHI wrote: > 'The City of London, London' also has no occurrences of any repeated phrase. Not sure the City would be particularly happy with that attribution. ;-) Its it sits on its own. Its own local authori

Re: Counting the number of repeated phrases in a column

2022-01-25 Thread David G. Johnston
On Tue, Jan 25, 2022 at 10:10 AM Shaozhong SHI wrote: > Anyone has got such a function to check out the number of occurrence of > any repeated phrases? > Not I. But I wouldn't be surprised that such an algorithm exists and that it has been implemented - in a language other than SQL or pl/pgsql.

Aw: Re: Counting the number of repeated phrases in a column

2022-01-25 Thread Karsten Hilbert
> How about split up the value into individual words and keep their orders? > add words up to form individual phrase and ensure that each phrase only > consists unique/distinct words > count repeated phrases afterward >   > How about this? Sure, if that serves your purpose ? So far, we (I?) can'

Re: Counting the number of repeated phrases in a column

2022-01-25 Thread Shaozhong SHI
How about split up the value into individual words and keep their orders? add words up to form individual phrase and ensure that each phrase only consists unique/distinct words count repeated phrases afterward How about this? Regards, David On Tue, 25 Jan 2022 at 17:22, Karsten Hilbert wrote:

Aw: Counting the number of repeated phrases in a column

2022-01-25 Thread Karsten Hilbert
> There is a short of a function in the standard Postgres to do the following: >   > it is easy to count the number of occurrence of words, but it is rather > difficult to count the number of occurrence of phrases. >   > For instance: >   > A cell of value:  'Hello World' means 1 occurrence a phra

Counting the number of repeated phrases in a column

2022-01-25 Thread Shaozhong SHI
There is a short of a function in the standard Postgres to do the following: It is easy to count the number of occurrence of words, but it is rather difficult to count the number of occurrence of phrases. For instance: A cell of value: 'Hello World' means 1 occurrence a phrase. A cell of value