Поиск города по почтовому коду

Опубликовано Mar 15, 2012 в Язык SQL | 10 коммент.

,

Поиск города по почтовому коду

Предположим, что территория страны поделена на части, и каждой из этих частей присвоен уникальный почтовый код (Zip Code). В стране имеются города большие, маленькие и совсем крошечные. Каждый город располагается на местности таким образом, что может покрывать несколько территорий с разными почтовыми кодами. Выглядит это, как показано на рисунке.

Соответствие городов и почтовых кодов описано таблицей со столбцами ZipCode и CityName, которая тоже приведена на рисунке. Нужно написать для этой таблицы SQL запрос с параметром, который соответствует почтовому коду. Результатом этого запроса должно быть название города, который покрывает наибольшее количество территорий с различными почтовыми кодами, в перечень которых попадает территория с заданным значением кода. Если описанному условию соответствует несколько городов – то из них выбирается один случайным образом. Примеры ввода и соответствующего вывода также имеются на рисунке.

Дополнение условия

Решение задачи в исходной постановке предложено. Теперь можно попробовать написать запрос, который параметра, соответствующего ZIP-коду содержать не будет, а выполнит указанную задачу ДЛЯ ВСЕХ значений ZIP-кодов, то есть в результате выдаст целиком таблицу, приведенную на рисунке справа внизу.

Скрипт создания исходных данных

CREATE TABLE [dbo].[ZipCityForPost](
	[Zip] [nvarchar](10) NOT NULL,
	[City] [nvarchar](10) NOT NULL,
 CONSTRAINT [PK_ZipCity] PRIMARY KEY CLUSTERED
(
	[Zip] ASC,
	[City] ASC
)WITH (PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip1',  N'City1')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip1 ', N'City2')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip1 ', N'City3')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip2 ', N'City1')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip3 ', N'City3')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip4 ', N'City3')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip5 ', N'City3')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip5 ', N'City4')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip6 ', N'City3')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip6 ', N'City4')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip7 ', N'City4')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip7 ', N'City5')
INSERT [dbo].[ZipCityForPost] ([Zip], [City])
VALUES (N'Zip7 ', N'City6')

 


Автор публикации:

10 Коммент. : “Поиск города по почтовому коду”

  1. SELECT TOP 1 City
    FROM
    (
    	SELECT 	City,COUNT(*) AS 'Cnt'
    	FROM	ZipCity
    	WHERE	City IN
    	(
    		SELECT	City 
    		FROM 	ZipCity
    		WHERE 	Zip = 'Zip1'
    	) 
    	GROUP BY City
    ) x
    ORDER BY x.Cnt DESC
  2. SQL Lover says:

    Мда… SQLя маловато совсем… Вы используете какие-нибудь СКЛ СУБД в проектах?

    • Галина says:

      Не все успеваем. Может вы задачку по SQL интересную предложите? С удовольствием опубликуем.

  3. Решение с временной таблицей

    GO
    DROP TABLE #Temp;
    GO
     
    DECLARE @Zip nvarchar(10);
    SET @Zip = 'Zip1';
     
    SELECT City , COUNT(Zip)AS Zipc 
    INTO #Temp 
    FROM dbo.ZipCityForPost 
    WHERE City IN(
        SELECT City 
        FROM Fulcrum.dbo.ZipCityForPost 
        WHERE    Zip=@Zip
    ) 
    GROUP BY City;
     
    SELECT TOP 1 City 
    FROM #Temp JOIN (
        SELECT MAX(Zipc)AS mZ 
        FROM #Temp
    )AS t 
    ON (t.mZ=Zipc)
    ORDER BY NEWID()
    • Галина says:

      Решение правильное. Оправдано, ли использование временной таблицы вместо подзапроса/табличной переменной – это вопрос довольно неоднозначный, есть как плюсы, так и минусы. Как по мне, так для этой задачи можно без нее обойтись – записей во временной таблице будет столько, сколько имеется городов для конкретного zip-кода. Можно прикинуть это число для настоящих данных, например по США или Канаде и, основываясь на нем выбирать, что оптимальнее.
      На реальных данных вопрос оптимизации будет стоять, так как запрос достаточно громоздкий, и если должен выполняться в On-Line режиме (клики в GUI), то им, скорее всего, придется заниматься.

  4. MS SQL Server:

    SELECT DISTINCT p.Zip, fn.City
    FROM ZipCityForPost p
    	CROSS APPLY (SELECT TOP 1 City FROM ZipCityForPost WHERE Zip = p.Zip GROUP BY City ORDER BY COUNT(*) DESC) AS fn

Оставить комментарий

Ваш адрес email не будет опубликован.


*