PostgreSQL: SQL のサンプル （コラムの一致数）

PostgreSQL で スキーマ sch015 内で ren015o003 というテーブル名を与えて、
ren015o003 と一番多くコラムが一致するテーブルと一致したコラムの数を表示するSQL

SQL

search_table.sql

set search_path=sch015;
SELECT
  t2.table_name, COUNT(*) AS matching_columns
FROM
  (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'ren015o003'
  ) t1
CROSS JOIN
  information_schema.columns t2
WHERE t1.column_name = t2.column_name
GROUP BY t2.table_name
ORDER BY matching_columns DESC
LIMIT 10;

実行結果

$ psql -U scott db_common < search_table.sql 
SET
 table_name | matching_columns 
------------+------------------
 ren015o003 |               35
 grp015012  |               18
 grp015011  |               17
(3 rows)

Python 版

.env

user='scott'
password='****'
data_base='db_common'

search_table.py

#! /usr/bin/python
#
#	search_table.py
#
#					Jun/11/2024
#
# --------------------------------------------------------
import sys
import os
import psycopg2
import psycopg2.extras
from dotenv import load_dotenv
#
from db_search import  db_search_proc
# --------------------------------------------------------
# [4]:
def db_process(schema,table):
	conn = psycopg2.connect("dbname=" + DATA_BASE + " user=" + USER + " password=" + PASSWORD)
#
	cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
#
	array_tables = db_search_proc(cur,schema,table)
#	print(array_tables)

	for unit in array_tables:
		print(unit[0],unit[1])

	conn.commit ()
#
	cur.close ()
	conn.close ()
#
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
#
schema=sys.argv[1]
table=sys.argv[2]
sys.stderr.write ("*** schema = %s ***\n" % schema)
sys.stderr.write ("*** table = %s ***\n" % table)
#
dotenv_path = '.env'
load_dotenv(dotenv_path)
USER=os.environ.get("user")
PASSWORD=os.environ.get("password")
DATA_BASE=os.environ.get("data_base")
#
db_process(schema,table)
#
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------

db_search.py

#
#	db_search.py
#
#					Jun/11/2024
#
# --------------------------------------------------------
import sys
import psycopg2
import psycopg2.extras
#
# --------------------------------------------------------
# [6]:
def db_search_proc(cur,schema,table_in):
	sql_str= "set search_path=sch015; "
#	sql_str += "select current_schema(); "
	sql_str += "SELECT t2.table_name, COUNT(*) AS matching_columns "
	sql_str += "FROM ( SELECT column_name "
	sql_str += "FROM information_schema.columns WHERE table_name = '"
	sql_str += table_in + "' ) t1 "
	sql_str += "CROSS JOIN information_schema.columns t2 "
	sql_str += "WHERE t1.column_name = t2.column_name "
	sql_str += "GROUP BY t2.table_name "
	sql_str += "ORDER BY matching_columns DESC "
	sql_str += "LIMIT 10;"
#
#	print(sql_str)
#
	cur.execute (sql_str)
	rows = cur.fetchall()
	array_tables = []
	for row in rows:
		array_tables.append([row[0],row[1]])
#
	if len(array_tables) == 0:
		sys.stderr.write("*** error *** %s ***\n" % table_in)
#
	return array_tables
# --------------------------------------------------------

実行結果

$ ./search_table.py sch015 ren015o003
*** 開始 ***
*** schema = sch015 ***
*** table = ren015o003 ***
ren015o003 35
grp015012 18
grp015011 17
*** 終了 ***