Определить товары которые покупали более 1 раза

Определить товары, которые еще никто не покупал SQL

Необходимо написать запрос, который выведет наименование и цену товаров, которые ещё никто не покупал (должен быть 1 товар). Нужно использовать в запросе NULL. Пишу такой запрос, но ничего не выводит:

SELECT product_name, price FROM orders JOIN orders_products ON orders.order_id = orders_products.order_id JOIN products ON orders_products.product_id = products.product_id JOIN buyers ON orders.buyer_id = buyers.buyer_id WHERE products.product_id IS NULL; 

Если написать IS NOT NULL, то выводятся все записи, кроме той, которой должна быть при IS NULL.
Отслеживать
задан 14 окт 2022 в 7:59
109 3 3 серебряных знака 12 12 бронзовых знаков

Необходимо написать запрос, который выведет наименование и цену товаров, которые ещё никто не покупал (должен быть 1 товар). Первые две таблицы для этого запроса не нужны в принципе. Задача решается элементарно путём использования WHERE NOT EXISTS. Нужно использовать в запросе NULL. Ооо! так это домашнее задание, оказывается? ну тогда LEFT JOIN WHERE IS NULL.

14 окт 2022 в 8:21
@Akina С LEFT тоже ничего не выводится.
14 окт 2022 в 8:53

@Akina Вот так написал: SELECT product_name, price FROM products LEFT JOIN orders_products ON products.product_id = orders_products.product_id WHERE products.product_id IS NULL;

Поиск и подсчет самых частых значений

Необходимость поиска наибольших и наименьших значений в любом бизнесе очевидна: самые прибыльные товары или ценные клиенты, самые крупные поставки или партии и т.д. Но наравне с этим, иногда приходится искать в данных не топовые, а самые часто встречающиеся значения, что хоть и звучит похоже, но, по факту, совсем не то же самое. Применительно к магазину, например, это может быть поиск не самых прибыльных, а самых часто покупаемых товаров или самое часто встречающееся количество позиций в заказе, минут в разговоре и т.п. В такой ситуации задачу придется решать немного по-разному, в зависимости от того, с чем мы имеем дело — с числами или с текстом.

Поиск самых часто встречающихся чисел

Предположим, перед нами стоит задача проанализировать имеющиеся данные по продажам в магазине, с целью определить наиболее часто встречающееся количество купленных товаров. Для определения самого часто встречающегося числа в диапазоне можно использовать функцию МОДА (MODE) :

Т.е., согласно нашей статистике, чаще всего покупатели приобретают 3 шт. товара. Если существует не одно, а сразу несколько значений, встречающихся одинаково максимальное количество раз (несколько мод), то для их выявления можно использовать функцию МОДА.НСК (MODE.MULT) . Ее нужно вводить как формулу массива, т.е. выделить сразу несколько пустых ячеек, чтобы хватило на все моды с запасом и ввести в строку формул =МОДА.НСК(B2:B16) и нажать сочетание клавиш Ctrl+Shift+Enter. На выходе мы получим список всех мод из наших данных:
Т.е., судя по нашим данным, часто берут не только по 3, но и по 16 шт. товаров. Обратите внимание, что в наших данных только две моды (3 и 16), поэтому остальные ячейки, выделенные «про запас», будут с ошибкой #Н/Д.

Частотный анализ по диапазонам функцией ЧАСТОТА

Если же нужно проанализировать не целые, а дробные числа, то правильнее будет оценивать не количество одинаковых значений, а попадание их в заданные диапазоны. Например, нам необходимо понять какой вес чаще всего бывает у покупаемых товаров, чтобы правильно выбрать для магазина тележки и упаковочные пакеты подходящего размера. Другими словами, нам нужно определить сколько чисел попадает в интервал 1..5 кг, сколько в интервал 5..10 кг и т.д. Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY) . Для нее нужно заранее подготовить ячейки с интересующими нас интервалами (карманами) и затем выделить пустой диапазон ячеек (G2:G5) по размеру на одну ячейку больший, чем диапазон карманов (F2:F4) и ввести ее как формулу массива, нажав в конце сочетание Ctrl+Shift+Enter:

Частотный анализ сводной таблицей с группировкой

Альтернативный вариант решения задачи: создать сводную таблицу, где поместить вес покупок в область строк, а количество покупателей в область значений, а потом применить группировку — щелкнуть правой кнопкой мыши по значениям весов и выбрать команду Группировать (Group) . В появившемся окне можно задать пределы и шаг группировки: . и после нажатия на кнопку ОК получить таблицу с подсчетом количества попаданий покупателей в каждый диапазон группировки:
Минусы такого способа:

• шаг группировки может быть только постоянным, в отличие от функции ЧАСТОТА, где карманы можно задать абсолютно любые
• сводную таблицу нужно обновлять при изменении исходных данных (щелчком правой кнопки мыши — Обновить), а функция пересчитывается автоматически «на лету»

Поиск самого часто встречающегося текста

Если мы имеем дело не с числами, а с текстом, то подход к решению будет принципиально другой. Предположим, что у нас есть таблица из 100 строк с данными о проданных в магазине товарах, и нам нужно определить, какие товары покупались наиболее часто?

Самым простым и очевидным решением будет добавить рядом столбец с функцией СЧЁТЕСЛИ (COUNTIF) , чтобы подсчитать количество вхождений каждого товара в столбце А:

Затем, само-собой, отсортировать получившийся столбец по убыванию и посмотреть на первые строчки.

Или же добавить к исходному списку столбец с единичками и построить по получившейся таблице сводную, подсчитав суммарное количество единичек для каждого товара:

Если исходных данных не очень много и принципиально не хочется пользоваться сводными таблицами, то можно использовать формулу массива:

Давайте разберем ее по кусочкам:

• СЧЁТЕСЛИ(A2:A20;A2:A20) – формула массива, которая ищет по очереди количество вхождений каждого товара в диапазоне A2:A100 и выдаст на выходе массив с количеством повторений, т.е., фактически, заменяет собой дополнительный столбец
• МАКС – находит в массиве вхождений самое большое число, т.е. товар, который покупали чаще всего
• ПОИСКПОЗ – вычисляет порядковый номер строки в таблице, где МАКС нашла самое большое число
• ИНДЕКС – выдает из таблицы содержимое ячейки с номером, который нашла ПОИСКПОЗ

Ссылки по теме

• Подсчет количества уникальных значений в списке
• Извлечение уникальных элементов из списка с повторами
• Группировка в сводных таблицах

Примеры SELECT (Transact-SQL)

В этой статье приведены примеры использования инструкции SELECT .

В этой статье требуется AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества Microsoft SQL Server.

А. Использование SELECT для получения строк и столбцов

В следующем примере приведены три примера кода. В ходе выполнения первого примера кода возвращаются все строки (предложение WHERE не указано), а также все столбцы (используется звездочка, * ) таблицы Product базы данных AdventureWorks2022 .

USE AdventureWorks2022; GO SELECT * FROM Production.Product ORDER BY Name ASC; -- Alternate way. USE AdventureWorks2022; GO SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк (предложение WHERE не задано) и подмножества столбцов ( Name , ProductNumber , ListPrice ) таблицы Product базы данных AdventureWorks2022 . Дополнительно выведено название столбца.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product ORDER BY Name ASC; GO 

В ходе выполнения данного примера кода происходит выдача всех строк таблицы Product , для которых линейки продуктов начинаются символом R и для которых длительность изготовления не превышает 4 дней.

USE AdventureWorks2022; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = 'R' AND DaysToManufacture < 4 ORDER BY Name ASC; GO 

B. Использование SELECT с заголовками столбцов и вычислениями

В ходе выполнения следующего примера возвращаются все строки таблицы Product . В результате выполнения первого примера выдаются все объемы продаж и скидки по всем продуктам. Во втором примере вычисляется годовой доход от продажи каждого вида продукции.

USE AdventureWorks2022; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName DESC; GO 

Данный запрос вычисляет доход от продажи по каждому виду продукции для каждого заказа.

USE AdventureWorks2022; GO SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ', p.Name AS ProductName FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID ORDER BY ProductName ASC; GO 

C. Использование DISTINCT с SELECT

В приведенном ниже примере для предотвращения получения повторяющихся заголовков используется оператор DISTINCT .

USE AdventureWorks2022; GO SELECT DISTINCT JobTitle FROM HumanResources.Employee ORDER BY JobTitle; GO 

D. Создание таблиц с помощью SELECT INTO

В следующем примере в базе данных #Bicycles создается временная таблица tempdb .

USE tempdb; GO IF OBJECT_ID(N'#Bicycles', N'U') IS NOT NULL DROP TABLE #Bicycles; GO SELECT * INTO #Bicycles FROM AdventureWorks2022.Production.Product WHERE ProductNumber LIKE 'BK%'; GO 

В данном примере создается постоянная таблица NewProducts .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY BULK_LOGGED; GO SELECT * INTO dbo.NewProducts FROM Production.Product WHERE ListPrice > $25 AND ListPrice < $100; GO ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL; GO 

Д. Использование сопоставленных вложенных запросов

Коррелированный запрос — это запрос, зависящий от результатов выполнения другого запроса. Этот запрос можно выполнять многократно, один раз для каждой строки, которая может быть выбрана внешним запросом.

В первом примере представлены семантически эквивалентные запросы для демонстрации различий в использовании ключевых слов EXISTS и IN . В обоих примерах приведены допустимые вложенные запросы, извлекающие по одному экземпляру продукции каждого наименования, для которых модель продукта — «long sleeve logo jersey» (кофта с длинными рукавами, с эмблемой), а значения столбцов ProductModelID таблиц Product и ProductModel совпадают.

USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product AS p WHERE EXISTS ( SELECT * FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND pm.Name LIKE 'Long-Sleeve Logo Jersey%' ); GO -- OR USE AdventureWorks2022; GO SELECT DISTINCT Name FROM Production.Product WHERE ProductModelID IN ( SELECT ProductModelID FROM Production.ProductModel AS pm WHERE p.ProductModelID = pm.ProductModelID AND Name LIKE 'Long-Sleeve Logo Jersey%' ); GO 

В следующем примере используется и извлекается IN один экземпляр первого имени и имени семьи каждого сотрудника, для которого указан 5000.00 бонус в SalesPerson таблице, и для которого идентификаторы сотрудников совпадают в Employee таблицах и SalesPerson таблицах.

USE AdventureWorks2022; GO SELECT DISTINCT p.LastName, p.FirstName FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN ( SELECT Bonus FROM Sales.SalesPerson AS sp WHERE e.BusinessEntityID = sp.BusinessEntityID ); GO 

Предыдущий вложенный запрос в этом операторе нельзя оценивать независимо от внешнего запроса. Он требует значения параметра Employee.EmployeeID , однако это значение меняется, когда ядро СУБД SQL Server обрабатывает строки в Employee .

Коррелированный вложенный запрос также может использоваться в предложении HAVING внешнего запроса. В данном примере осуществляется поиск моделей продуктов, для которых максимальная цена в каталоге в два раза превышает среднюю цену по нему.

USE AdventureWorks2022; GO SELECT p1.ProductModelID FROM Production.Product AS p1 GROUP BY p1.ProductModelID HAVING MAX(p1.ListPrice) >= ( SELECT AVG(p2.ListPrice) * 2 FROM Production.Product AS p2 WHERE p1.ProductModelID = p2.ProductModelID ); GO 

В этом примере используются два сопоставленных вложенных запроса для поиска имен сотрудников, которые продали определенный продукт.

USE AdventureWorks2022; GO SELECT DISTINCT pp.LastName, pp.FirstName FROM Person.Person pp INNER JOIN HumanResources.Employee e ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN ( SELECT SalesPersonID FROM Sales.SalesOrderHeader WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail WHERE ProductID IN ( SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42' ) ) ); GO 

F. Использование GROUP BY

В следующем примере находится общий объем продаж для каждого заказа в базе данных.

USE AdventureWorks2022; GO SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY SalesOrderID ORDER BY SalesOrderID; GO 

Так как в запросе используется предложение GROUP BY , то для каждого заказа выводится только одна строка, содержащая общий объем продаж.

G. Использование GROUP BY с несколькими группами

В данном примере вычисляются средние цены и объемы продаж за последний год, сгруппированные по коду продукта и идентификатору специального предложения.

USE AdventureWorks2022; GO SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail GROUP BY ProductID, SpecialOfferID ORDER BY ProductID; GO 

H. Использование GROUP BY и WHERE

В следующем примере после извлечения строк, содержащих цены каталога, превышающие $1000 , происходит их разделение на группы.

USE AdventureWorks2022; GO SELECT ProductModelID, AVG(ListPrice) AS [Average List Price] FROM Production.Product WHERE ListPrice > $1000 GROUP BY ProductModelID ORDER BY ProductModelID; GO 

I. Использование GROUP BY с выражением

В следующем примере производится группировка с помощью выражения. Можно сгруппировать по выражению, если выражение не включает агрегатные функции.

USE AdventureWorks2022; GO SELECT AVG(OrderQty) AS [Average Quantity], NonDiscountSales = (OrderQty * UnitPrice) FROM Sales.SalesOrderDetail GROUP BY (OrderQty * UnitPrice) ORDER BY (OrderQty * UnitPrice) DESC; GO 

J. Использование GROUP BY с ORDER BY

В следующем примере для каждого типа продуктов вычисляется средняя цена, а также осуществляется сортировка полученных результатов по возрастанию.

USE AdventureWorks2022; GO SELECT ProductID, AVG(UnitPrice) AS [Average Price] FROM Sales.SalesOrderDetail WHERE OrderQty > 10 GROUP BY ProductID ORDER BY AVG(UnitPrice); GO 

K. Использование предложения HAVING

В первом из приведенных ниже примеров показывается использование предложения HAVING с агрегатной функцией. В нем производится группировка строк таблицы SalesOrderDetail по коду продукта, а также удаляются строки, соответствующие продуктам, для которых средний объем заказа не превышает пяти. Во втором примере показывается использование предложения HAVING без агрегатной функции.

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID; GO 

В данном запросе внутри предложения LIKE используется предложение HAVING .

USE AdventureWorks2022; GO SELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetail GROUP BY SalesOrderID, CarrierTrackingNumber HAVING CarrierTrackingNumber LIKE '4BD%' ORDER BY SalesOrderID ; GO 

L. Использование HAVING и GROUP BY

В следующем примере показано использование предложений GROUP BY , HAVING , WHERE и ORDER BY в одной инструкции SELECT . В результате его выполнения в группах и сводных значениях не учитываются строки, соответствующие продуктам с ценами выше $25 и средним объемом заказов ниже 5. Также осуществляется сортировка результатов по ProductID .

USE AdventureWorks2022; GO SELECT ProductID FROM Sales.SalesOrderDetail WHERE UnitPrice < 25.00 GROUP BY ProductID HAVING AVG(OrderQty) >5 ORDER BY ProductID; GO 

M. Использование HAVING с СУММ и AVG

В следующем примере производится группировка строк таблицы SalesOrderDetail по коду продукта, а затем выводятся только те группы, для которых общий объем продаж составляет более $1000000.00 , а средний объем заказа не превышает 3 .

USE AdventureWorks2022; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3; GO 

Чтобы просмотреть продукты с общим объемом продаж, превышающих $2000000.00 , используйте следующий запрос:

USE AdventureWorks2022; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00; GO 

Если вы хотите убедиться в наличии не менее 1500 элементов, участвующих в вычислениях для каждого продукта, используйте HAVING COUNT(*) > 1500 для устранения продуктов, возвращающих итоги для меньшего количества 1500 проданных элементов. Этот запрос выглядит следующим образом.

USE AdventureWorks2022; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500; GO 

О. Использование указания оптимизатора INDEX

В следующем примере показаны два способа использования указания оптимизатора INDEX . В первом примере показано, как принудительно принудить оптимизатора использовать некластеризованный индекс для получения строк из таблицы. Во втором примере выполняется проверка таблицы с помощью индекса 0.

USE AdventureWorks2022; GO SELECT pp.FirstName, pp.LastName, e.NationalIDNumber FROM HumanResources.Employee AS e WITH (INDEX (AK_Employee_NationalIDNumber)) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Force a table scan by using INDEX = 0. USE AdventureWorks2022; GO SELECT pp.LastName, pp.FirstName, e.JobTitle FROM HumanResources.Employee AS e WITH (INDEX = 0) INNER JOIN Person.Person AS pp ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO 

M. Использование OPTION и подсказок GROUP

В следующем примере демонстрируется совместное использование предложений OPTION (GROUP) и GROUP BY .

USE AdventureWorks2022; GO SELECT ProductID, OrderQty, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (HASH GROUP, FAST 10); GO 

O. Использование указания запроса UNION

В следующем примере используется указание запроса MERGE UNION .

USE AdventureWorks2022; GO SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e1 UNION SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours FROM HumanResources.Employee AS e2 OPTION (MERGE UNION); GO 

P. Использование UNION

При выполнении следующего примера в результирующий набор включается содержимое столбцов ProductModelID и Name таблиц ProductModel и Gloves .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO -- Here is the simple union. USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

В. Использование SELECT INTO с UNION

При выполнении следующего примера предложение INTO во второй инструкции SELECT указывает, что в таблице с именем ProductResults содержится итоговый результирующий набор объединения заданных столбцов таблиц ProductModel и Gloves . Таблица Gloves была создана в результате выполнения первой инструкции SELECT .

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.ProductResults', 'U') IS NOT NULL DROP TABLE dbo.ProductResults; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO USE AdventureWorks2022; GO SELECT ProductModelID, Name INTO dbo.ProductResults FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO SELECT ProductModelID, Name FROM dbo.ProductResults; 

R. Использование UNION двух операторов SELECT с ORDER BY

При использовании предложения UNION необходимо соблюдать порядок следования определенных параметров. В следующем примере показаны случаи правильного и неверного использования UNION в двух инструкциях SELECT , в которых необходимо переименовать столбцы на выходе.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.Gloves', 'U') IS NOT NULL DROP TABLE dbo.Gloves; GO -- Create Gloves table. SELECT ProductModelID, Name INTO dbo.Gloves FROM Production.ProductModel WHERE ProductModelID IN (3, 4); GO /* INCORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) ORDER BY Name UNION SELECT ProductModelID, Name FROM dbo.Gloves; GO /* CORRECT */ USE AdventureWorks2022; GO SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID NOT IN (3, 4) UNION SELECT ProductModelID, Name FROM dbo.Gloves ORDER BY Name; GO 

S. Использование UNION трех инструкций SELECT для отображения эффектов ALL и круглых скобок

В следующих примерах используются UNION для объединения результатов трех таблиц, которые имеют одинаковые пять строк данных. В первом примере используется предложение UNION ALL , в результате чего выдаются все 15 строк. Второй пример используется без ALL исключения повторяющихся UNION строк из объединенных результатов трех SELECT операторов и возвращает пять строк.

В третьем примере с первым предложением ALL используется ключевое слово UNION , а во втором предложении UNION вместо ключевого слова ALL используются скобки. Второй UNION обрабатывается сначала, так как он находится в скобках, и возвращает пять строк, так как ALL параметр не используется и дубликаты удаляются. Эти пять строк объединяются с результатами первого SELECT с помощью UNION ALL ключевое слово. В данном случае повторяющиеся строки двух множеств, состоящих из пяти строк, не удаляются. Окончательный результат состоит из 10 строк.

USE AdventureWorks2022; GO IF OBJECT_ID('dbo.EmployeeOne', 'U') IS NOT NULL DROP TABLE dbo.EmployeeOne; GO IF OBJECT_ID('dbo.EmployeeTwo', 'U') IS NOT NULL DROP TABLE dbo.EmployeeTwo; GO IF OBJECT_ID('dbo.EmployeeThree', 'U') IS NOT NULL DROP TABLE dbo.EmployeeThree; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeOne FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeTwo FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeThree FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = pp.BusinessEntityID WHERE LastName = 'Johnson'; GO -- Union ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION ALL SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree; GO SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeOne UNION ALL ( SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwo UNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree ); GO 

Связанный контент

• CREATE TRIGGER (Transact-SQL)
• CREATE VIEW (Transact-SQL)
• DELETE (Transact-SQL)
• EXECUTE (Transact-SQL)
• Выражения (Transact-SQL)
• INSERT (Transact-SQL)
• LIKE (Transact-SQL)
• Операторы set — UNION (Transact-SQL)
• Операторы set — EXCEPT и INTERSECT (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)
• PathName (Transact-SQL)
• SELECT — предложение INTO (Transact-SQL)

Обратная связь

Были ли сведения на этой странице полезными?

Повторные продажи - наш опыт

Сегодня поговорим о повторных продажах на примере интернет-магазина по доставке цветов, методе их определения, стимулировании и способах увеличения.

Начнем с цифр. Маржинальность «цветочного бизнеса» примерно составляет 35%. Средний чек по Москве и МО для нашего проекта – 4500 руб. Следовательно, прибыль с каждого заказа составляет ~1600 руб. Стоимость закрытого заказа (продажи) из контекстной рекламы по тому же региону варьируется от 500 до 1300 руб. (в зависимости от типа рекламных кампаний). Средняя по больнице – 1000 руб. за неделю. Таким образом, в лучшем случае владелец бизнеса зарабатывает с каждого заказа ~500 руб., а в худшем – 0 руб. или работает даже в небольшой минус.

Конкуренция в этой тематике запредельная, особенно по данному региону. И она постоянно растет, источники дорожают, новый клиент с каждым годом обходится все дороже и дороже. Поэтому помимо традиционных источников привлечения клиентов (SEO, контекстная реклама) каждый здравомыслящий руководитель рано или поздно задумается о расширении охвата аудитории путем подключения дополнительных более дешевых каналов: e-mail, социальные сети, партнерские программы и т.д. Да, не всегда и не везде вышеописанные источники дороже или дешевле остальных. Дьявол кроется в деталях.

Наряду с подключением дополнительных источников, нужно уметь правильно работать с уже существующей клиентской базой - учиться продавать повторно. Ведь привлекая клиента и не зарабатывая с первого заказа ничего (даже в той же контекстной рекламе Яндекс.Директ или Google AdWords), при правильно выстроенных процессах этот самый клиент в будущем может купить у нас что-нибудь еще. LTV (Lifetime Value) вырастет, а расходы на него практически не изменятся.

Тогда мы решили посчитать % повторных продаж в интернет-магазине за весь срок его существования. Скажу сразу, никакой отдельной работы по этому направлению никогда не было. Более того, мы вообще не знали, покупают ли у нас повторно или нет. Хотя бизнес существовал уже более 2 лет.

Как считать повторные продажи?

Вопрос хороший. Наш интернет-магазин работает на 1С-Битрикс. В ней есть рабочая панель в виде таблицы, настраиваемыми полями и все необходимой информацией по заказу.

Админка в 1С-Битрикс

Каждый ID заказа – новый строчка в таблице, с новой суммой, статусом, позицией и т.д. Чтобы посчитать клиентов, которые покупали у нас повторно, необходимо определить метрику, по которой мы сможем «склеить» эти ID заказов.

Их у нас было 2:

E-mail нам не подходил, поскольку:

• люди звонили по телефону и оформляли заказ, а оператор сам заносил его в админку (по телефону мы не спрашивали e-mail адрес, поскольку под диктовку есть большая вероятность неправильной записи, получить невалидный ящик или отказов говорить со стороны клиента);
• люди, оформляя заказ на сайте, часто заполняли это поле несуществующим почтовым ящиком.

Остановились на телефоне. Он у нас делился на еще два варианта: телефон покупателя и телефон получателя. И часто бывает, что это совершенно разные люди. Мы решили привязаться к телефону получателя, поскольку это более точное касание курьера с клиентом (перед поездкой он звонит, договаривается о встрече и т.д.).

Все, решили. Телефон покупателя. Но данное поле при заполнении на сайте не было у нас стандартизовано, и поэтому телефон мог быть вида +7(916)XXX-XX-XX, 8(916)XXXXXXXXX и т.д. Теперь необходимо было привести их в один вид.

С данной задачей справился внештатный разработчик, который выполнил не только ее, но и также написал скрипт, который позволяет смотреть % повторных продаж.

Повторные продажи – это покупатели из нашей базы с 2 заказами и более со статусом заказа «Выполнено», а % повторных продаж – отношение покупателей с 2 заказами и более к общему числу всех заказов со статусом «Выполнено» за определенный период времени.

Таким образом, мы получили следующие цифры:

Отчет о повторных продажах

• Заказы (19735) – всего заказов со статусом «Выполнен» за весь период существования интернет-магазина;
• Покупателей с 1 заказом (8814);
• Покупателей с 2 и более заказов (1378);
• % повторных заказов (6,98%) – 1378 / 19735.

Данные доступны по каждому месяцу и объединены по номеру телефона покупателя. Если хочется посмотреть содержимое заказа, то можно раскрыть каждого клиента и посмотреть, что он купил.

Объединенные заказы по покупателям

Далее мы построили таблицу по каждому интервалу, начиная с самого первого месяца. Получилось:

Сводная таблица по % повторных продаж

Из этой таблицы видно, что в течение целого года (с июля 2015 по октябрь 2016 года) как таковых повторных заказов не было. Стабильный рост начался с ноября 2016 года и до сегодняшнего дня медленно, но верно продолжается. Последний отчетный период был за март 2018 года, и он составил ~7%.

Динамика % повторных продаж

Цифра не такая уж и большая. Однако мы никогда не придавали значения повторным продажам и не совершали никаких действий, направленных на удержание/сохранение клиента, а также побуждение его приобрести у нас что-нибудь еще.

Да, у нас проводятся акции, мы дарим купоны на первый заказ, подарок при заказе от определенной суммы, есть накопительная программа в 3%, 5% и 7% при последующих заказах. Но все это было бессистемно.

Клиентская база есть, но с ней нужно уметь правильно работать. С недавнего времени мы ввели e-mail маркетинг и теперь рассылаем нашим клиентам письма с акциями, новинками, популярными товарами.

Пример e-mail рассылки

Как показала практика, бесполезно присылать одно и тоже сообщение разным людям – мужчинам, женщинам. При работе с уже существующей базой необходима персонализация потребителей. Лучше всего работает сегментация пользователей на основании исторических данных о просмотренных товарах на сайте – предлагать им то, что они с наибольшей вероятностью приобретут в следующий раз или что их может заинтересовать. Информацию о просмотренных страницах на сайте можно узнать в том же Google Analytics в статистике по пользователям, а «умную» рассылку сделать в одном из сервисов автоматизации маркетинга. Например, используя Carrot Quest, Retail Rocket, Mindbox и др. Даже простое разделение клиентов по полу и отправка 2 разных писем – верный шаг на пути к повышению конверсии при повторных заказах. Периодичность (частота) рассылок тоже играет важную роль. Мы стараемся делать их не более 4 в месяц (1 раз в неделю).

Можно настроить цепочку триггерных писем с различными сценариями. Например, после совершения покупки (дополнительно к сообщению с № заказа) отправить письмо и предложить скидку на последующие заказы, а также с просьбой подписаться на социальные сети или предложить сопутствующие товары к совершенной покупке. Можно настроить отправку писем на определенные значимые праздники, напоминания о том, что скоро у вашего близкого человека будет знаменательный день и его следовало бы поздравить. Последнее решается внедрением дополнительного поля на сайте при оформлении заказа – можно просить указывать пользователей ближайшие даты дней рождений родственников, чтобы в будущем своевременно оповещать их об этом. Не всегда такой метод работает, поскольку при первой покупке новый клиент вряд ли будет делиться такой информацией с пока еще незнакомым для него интернет-магазином.

Также можно отправлять письма при совершении различных действий – «Пользователь просматривал каталог», «Клиент давно не посещал сайт», «Посетитель положил продукт в корзину» и т.д. Выбор того или иного варианта зависит от целей и поведении потенциальных клиентов на сайте.

Месяц назад я был на Data Science Weekend 2018. Там Артем Просветов из CleverDATA рассказывал о предиктивных моделях и рекомендательных системах для Beauty-маркетинга. Видеозапись выступления можно посмотреть в Facebook по ссылке. Если кратко, то спикер делился опытом работы по оптимизации маркетинговых коммуникаций на примере интернет-магазина косметики. Эта задача в данном проекте решалась с помощью нейронной сети.

Также многие компании в своей практике используют «звонки вежливости» - после покупки звонят клиенту и интересуются, понравился ли товар, осуществилась ли доставка вовремя, все ли его устраивает, нет ли какого-либо недовольства, были ли вежливы курьеры и т.д. Клиент чувствует заботу и интерес со стороны компании, что создает хорошее впечатление и помогает в дальнейшем осуществлять повторные продажи.

На собственном опыте мы убедились, что такой метод не всегда работает так, как изначально должен. Очень часто телефоны пользователей попадают в различные базы (финансовые пирамиды, брокерские конторы и т.д.), и когда им звонят в очередной раз и «навязчиво» просят оценить работу какой-то компании, а человек в этот момент занят делом, не в состоянии говорить или ему просто неохота оставлять отзыв, получается совершенно обратная реакция. Вместо позитива и хвалебных слов мы слышим: «Не звоните мне больше», «Удалите меня из базы» и т.д. Нет, мы не говорим, что это вообще не работает и так делать не стоит. Просто обзванивали так какое-то время своих клиентов и получали такие ответы. Тем более начать можно по-разному. И курьер во время доставки имеет возможность передать покупателю информацию о том, что завтра ему позвонят и попросят оценить качество работы интернет-магазина, и оператор по телефону беспрепятственно может сообщить это. Таким образом, мы предупредим клиента о грядущем звонке и это упростит дальнейшее общение.

Есть и другой способ – вместо звонков отправлять e-mail письма с просьбой оценить качество работы, заполнив небольшую форму на сайте или через сервис онлайн-опросов. Например, использовать для таких задач Google Формы. Вариантов много.

Поддерживать контакт с клиентом можно и через SMS-сообщения. После заказа отправить специальное предложение на следующую покупку или уведомить о программе лояльности и накопленных бонусах. Практика также показала, что традиционные проценты % на заказы работают гораздо хуже, чем конкретные денежные эквиваленты. Ведь гораздо приятнее, когда вам на почту прилетает письмо о том, что у вас есть реальная скидка в размере 500 руб. на следующий заказ, нежели какая-то мнимая 5% скидка на покупку. Здесь уже огромную роль играет психологический фактор – человек знает, что у него в интернет-магазине лежат деньги, которыми в любой момент можно воспользоваться. А скидка? Скидка так, греет душу, но не побуждает потратить деньги, в отличие от первого варианта.

Еще одним распространенным способом вернуть пользователей на сайт, повысить лояльность и побудить быстрее принять решение о покупке – это push-уведомления. Используя различные сервисы автоматизации, вы можете настроить их таким образом, чтобы люди возвращались к вам при каком-то значимом обновлении. Например, вы опубликовали новую статью на сайте (по аналогии с моим блогом). Отправляете подписавшимся новый push. Пользователи будут уведомлены об обновлении и с большей долей вероятности перейдут по ссылке на сайт. Или устраиваете акцию до конца дня! Так сообщите об этом своим подписчикам.

Социальные сети - очень полезный и эффективный способ работы с аудиторией, как с новой, так и с уже и сформировавшейся. Конкурсы с подарками за репост/лайки, коллаборации с блогерами, публикации реальных отзывов как фото, так и видео - все это позитивно отражается на лояльности пользователей и будущих продажах.

И самое последнее и «продвинутое», что сейчас можно делать – это менять блоки и контент на сайте в зависимости от предпочтений пользователей и истории просмотренных страниц.

Хочется добавить и то, что в рамках данного интернет-магазина сейчас используются e-mail рассылки и SMS-уведомления. Проделанная работа дала понять нам то, что % повторных продаж является важной метрикой для бизнеса, которой нельзя пренебрегать и которая требует комплексного подхода. А формирование положительного имиджа компании в глазах покупателей является основной функцией, и использование различных вышеописанных методик и контактов с существующей клиентской базой обязательно приведет к увеличению доли повторных продаж.