Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbktv-00053Q-PK for pgsql-sql@arkaria.postgresql.org; Tue, 05 Apr 2022 15:15:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nbktu-0004Mi-OI for pgsql-sql@arkaria.postgresql.org; Tue, 05 Apr 2022 15:15:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbktu-0004MY-BD for pgsql-sql@lists.postgresql.org; Tue, 05 Apr 2022 15:15:22 +0000 Received: from premium22-1.web-hosting.com ([68.65.122.103]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nbktp-00030p-Es for pgsql-sql@lists.postgresql.org; Tue, 05 Apr 2022 15:15:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=oY7oz5jyVcVTLq43Hzn5UDmp14lIbSjud1Y16v4EXUE=; b=tjjJMYEyexgpTH1WWmz7U22gH4 t0V322HyKKwcg9ejr/80rpu6vy8bvRryZKI5ftuBmtKCL0Q0WD/QNDNS7eyNAHVzHOBGkVcU1/p/q FVJzm9WsCOhhYdMBY6EuR1qikXqYIPxtrHPygd8ObSEBdWC9KVlve6wHCWvZDMCpbksks/kc2tnmH lsCCYYfmJk32XC8un0fLz2iq8Xab1UGzUxUxYD7BzycaxeCLk4FOeym6mBMAwz6pdoB8DXj47B2Ax xfOeqVSExR5OgXjtemg8kY+k8Rpha5IzR1I/Cauuy9ob4AdpRYc2a0xE+W/UwpccRu1gm96wqR/95 u4ZOS6sg==; Received: from mail-qv1-f47.google.com ([209.85.219.47]:41627) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1nbktg-00G8iZ-OU for pgsql-sql@lists.postgresql.org; Tue, 05 Apr 2022 11:15:14 -0400 Received: by mail-qv1-f47.google.com with SMTP id cs16so4520358qvb.8 for ; Tue, 05 Apr 2022 08:15:08 -0700 (PDT) X-Gm-Message-State: AOAM533bMmUVxrpHomdpwzP3BczJObdt/SsTpaHB64W+Ux8QudYPTCOn BtXMBFVuaeseo0+64WiQQTAlFMKa49WDVZdxM5o= X-Google-Smtp-Source: ABdhPJw5NOefQ96p4yX7toz6NYWWsugTl7gazQXBHkS/YjBeaGgiRCQKzkw+3o22/53pnKE/vXMC8Jt5+xAqQa2Zo/g= X-Received: by 2002:ad4:4ee5:0:b0:443:dd3d:f587 with SMTP id dv5-20020ad44ee5000000b00443dd3df587mr3316394qvb.123.1649171708026; Tue, 05 Apr 2022 08:15:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Tue, 5 Apr 2022 08:14:57 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Histogram question. To: Jian He Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000d4a79905dbe9b5ed" X-OutGoing-Spam-Status: No, score=4.9 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d4a79905dbe9b5ed Content-Type: text/plain; charset="UTF-8" On Tue, Apr 5, 2022 at 7:35 AM Jian He wrote: > Queries in PostgreSQL: 2. Statistics : Postgres Professional > > > > > SELECT sum(s.most_common_freqs[ array_position((s.most_common_vals::text:: > text[]),v) ]) FROM pg_stats s, unnest(s.most_common_vals::text::text[]) v > WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no'; > > *return 0.6762. * > > SELECT sum(s.most_common_freqs[ array_position((s.most_common_vals::text:: > text[]),v) ]) FROM pg_stats s, unnest(s.most_common_vals::text::text[]) v > WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no' AND v > > '30C'; > > return *0.2127* > > SELECT round( reltuples * ( 0.2127 -- from most common values + (1 - > 0.6762 - 0) * (49 / 100.0) -- from histogram )) FROM pg_class WHERE > relname = 'boarding_passes'; > > the above mentioned query, the part I don't understand is *49/100.* > > I believe the exercise is intended to create a set of histograms based on data values over a series of intervals. The 49/100 (if I'm reading the source material correctly) refers to finding all the boarding passes in the lower 49 of 100 intervals. I didn't bother to read what the interval definition is, but I think that's what the "49" is referring to.. --000000000000d4a79905dbe9b5ed Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Tue, Apr 5, 2022 at 7:35 AM Jian H= e <hejian.mark@gmail.com>= ; wrote:



<= div>SELECT sum(s.most_common_freqs[ array_position((s.most_common_vals::text::text[]),v) ]) FROM pg_stats s, unnest(s.most_common_vals::text::text[= ]) v WHERE s.tablename =3D 'boarding_passes' AND s.attnam= e =3D 'seat_no'= ;

return= 0.6762.=C2=A0

SELECT sum(s.most_common_f= reqs[ array_position((s.most_common_vals::text::text[]),v) ]) FROM pg_stats s, unnest(s.most_common_vals::text::text[= ]) v WHERE s.tablename =3D 'boarding_passes' AND s.attnam= e =3D 'seat_no'= AND v > '= 30C';

return 0.2127

SELECT round( relt= uples * ( 0.2127 -- fro= m most common values + (1 - 0.6762= - 0) * (49 / 100.0) -- from histogram )) FROM pg_class WHE= RE relname =3D 'boarding_passes';<= /span>

the above mentioned query, the p= art I don't understand is 49/100.


I believe the exercise= is intended to create a set of histograms based on data values over a seri= es of intervals. The 49/100 (if I'm reading the source material correct= ly) refers to finding all the boarding passes in the lower 49 of 100 interv= als. I didn't bother to read what the interval definition is, but I thi= nk that's what the "49" is referring to..
--000000000000d4a79905dbe9b5ed--