X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8F7E2D1D2D0 for ; Thu, 11 Mar 2004 09:01:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 63941-08 for ; Thu, 11 Mar 2004 05:01:49 -0400 (AST) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr1.postgresql.org (Postfix) with ESMTP id EB858D1D282 for ; Thu, 11 Mar 2004 05:01:37 -0400 (AST) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.12.10/8.12.10) with ESMTP id i2B91bQT010183; Thu, 11 Mar 2004 12:01:37 +0300 (MSK) Date: Thu, 11 Mar 2004 12:01:37 +0300 (MSK) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Rajesh Kumar Mallah Cc: pgsql-sql@postgresql.org Subject: Re: Tsearch2 question: getting histogram of the vector elements In-Reply-To: <404F7269.9010301@trade-india.com> Message-ID: References: <404F7269.9010301@trade-india.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=koi8-r Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/129 X-Sequence-Number: 17228 Read http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes ("Check words statistics") tsearch2 comes with stat function. for example - top 10 words from apod (Astronomy picture of the day): select * from stat('select ti from apod') order by ndoc desc, nentry desc,word limit 10; word | ndoc | nentry --------+------+-------- year | 1283 | 2064 star | 1273 | 3908 imag | 1267 | 1967 light | 1232 | 2206 pictur | 1177 | 1442 earth | 1059 | 1798 visibl | 992 | 1259 bright | 936 | 1335 котор | 903 | 1441 эт | 879 | 1397 Oleg On Thu, 11 Mar 2004, Rajesh Kumar Mallah wrote: > > Greetings! > > My original problem is to de duplicate a list of around 0.3 million > company names. > > Since a company name can be potentially (mis)spelt in numerous ways > exactmatch > obviously wont work. > > To make the searches faster i am using tsearch. For each company name i > want to > search other companies whose name is similar to the company in question. > > Since inclusion of all the vector elements of a given company reduces the > chance of matching i am thinking of excluding the high frequency words > from the query. > > Hence i need to find the high frequency elements like say 'consulting' , > 'limited' , 'Private' > 'Industries' that occur commonly in company names. > > In my table i have populated the co_name_vec feild as > strip(to_tsvector(co_name)) > can anyone help me analyzing the co_name_vec for the high frequency words? > > Also i would like to know alternate / better solution to this problem. > > > Regds > Mallah. > > > > SAMPLE DATA. > > +-----------------------------------------------------+----------------------------------------------------------+ > | co_name > | co_name_vec | > +-----------------------------------------------------+----------------------------------------------------------+ > | European Trade Partner & Consulting | 'trade' > 'consult' 'partner' 'european' | > | Gulbrandsen Chemicals Pvt. Ltd. | 'ltd' 'pvt' > 'chemic' 'gulbrandsen' | > | Govt. of Karnataka, Vision Group on Biotechnology | 'govt' 'group' > 'vision' 'karnataka' 'biotechnolog' | > | Digital Globalsoft Ltd. (A Hewlett Packard Company) | 'ltd' 'digit' > 'compani' 'hewlett' 'packard' 'globalsoft' | > | Shanon Construction Material Industries | 'materi' > 'shanon' 'industri' 'construct' | > | singpore india trade rsources company | 'india' 'trade' > 'rsourc' 'compani' 'singpor' | > | RGV TELECOM CONSULTANTS PVT. LTD. | 'ltd' 'pvt' > 'rgv' 'consult' 'telecom' | > | avid information search and documents (p) ltd. | 'p' 'ltd' 'avid' > 'inform' 'search' 'document' | > | Tavant Technologies India (P) Ltd. | 'p' 'ltd' > 'india' 'tavant' 'technolog' | > | Maschinen Fabrik (India) Pvt. Ltd | 'ltd' 'pvt' > 'india' 'fabrik' 'maschinen' | > | Manishri Refractories and Ceramics Pvt. Ltd. | 'ltd' 'pvt' > 'ceram' 'manishri' 'refractori' | > | xavier export import management institute | 'manag' 'export' > 'import' 'xavier' 'institut' | > | Best InformationTechnology ltd. | 'ltd' 'best' > 'informationtechnolog' | > | FutureCalls Technology Private Limited | 'limit' 'privat' > 'futurecal' 'technolog' | > | mak controls and systems pvt ltd | 'ltd' 'mak' > 'pvt' 'system' 'control' | > | NATIONAL RESEARCH CENTRE FOR CASHEW | 'centr' 'cashew' > 'nation' 'research' | > | The Madras Aluminium Company Ltd. | 'ltd' 'madra' > 'compani' 'aluminium' | > | Shriram Institute for Industrial Research | 'shriram' > 'industri' 'institut' 'research' | > | All India Carpet Trade Fair Committee | 'fair' 'india' > 'trade' 'carpet' 'committe' | > | Tuff Security & Allied Services | 'alli' 'tuff' > 'secur' 'servic' | > +-----------------------------------------------------+----------------------------------------------------------+ > (20 rows) > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83