ایجاد محصول پیشنهادی

سلام
وقت بخیر
سایت فروش رستوران هست و میخواهیم برای هر محصول، محصول پیشنهادی مشخص شود.
برای هر محصول لیست محصولاتی که با محصول مورد نظر در یک سفارش بوده اند به تعداد تکرار در سفارش مرتب شوند
این شرایط بدون در نظر گرفتن CustomerId هست
مثال زیر: یعنی هر مشتری که جوجه سفارش میده حتما ماست هم سفارش میده پس من در محصول پیشنهادی جوجه کباب باید ماست رو بزارم
سالاد هم باید بزارم چون از 3 تا مشتری دو تا شون که جوجه سفارش دادن سالاد هم سفارش دادن
زیتون و کوکا هم میتونه در انتهای سبد خرید پیشنهادی باشه چون به هر حال از 3 نفری حداقل یک نفر یکی از این محصولات رو سفارش داده

جوجه کباب ماست سالاد زیتون کوکا 1 1 1 1 0 1 1 0 0 1 1 1 1 0 0

اسکریپتی که من نوشتم ولی جواب نداد به این صورت هست
دوستان اگر کسی میتونه راهنمایی کنه ممنون میشم
WITH order_items_counted AS
(
SELECT
oi.id,
p.ProductId,
p.ProductName,
oi.Units,
COUNT(p.ProductId) OVER (PARTITION BY oi.id) AS product_count -- Counts how many products are in each order
FROM ProductItems p
inner join OrderItems oi on p.OrderItemId=oi.Id
),
potential_matches AS
(
SELECT DISTINCT
O1.Id,
O1.product_count,
COUNT(O1.id) OVER (PARTITION BY O1.Id, O2.Id) AS matched_row_count -- Counts how many order matched
FROM order_items_counted AS O1
INNER JOIN order_items_counted AS O2
ON O1.Id <> O2.Id -- Different Orders
AND O1.product_count = O2.product_count -- With the same Products
AND O1.ProductId = O2.ProductId -- Which are the same Products
--AND O1.Units = O2.Units -- With the same Quantities
)


-- get matches
SELECT DISTINCT
Id
FROM potential_matches AS exact_matches
WHERE product_count = matched_row_count

به نظرم بدست آوردن تعداد محصولات جانبی سفارش داده شده اشتباه است. باید تعداد فاکتورهایی که دارای اون محصولات هستند رو بدست بیارید.
اگر غذاها و هم مخلفات در یک جدول ProductItems تعریف شده اند ، باید جدول سفارش رو با خودش join کنید و حاصل رو براساس عذای اصلی و مخلفات گروهبندی کنید تا تعداد رو بتونید بدست بیارید.
اسکریپت جداول رو قرار ندادید ولی نمونه زیر کمک میکنه که تعداد هر جفت محصولی که سفارش داده شده اند رو بدست بیارید.

SELECT OrderDetails.ProductID, OD.ProductID as odProductID, count(OrderDetails.OrderID)

FROM OrderDetails

join OrderDetails as OD on (OrderDetails.OrderID = OD.OrderID and OrderDetails.ProductID <> OD.ProductID and OrderDetails.OrderDetailID < OD.OrderDetailID)

group by OrderDetails.ProductID,  OD.ProductID

order by OrderDetails.OrderID

میتونید در جدول محصولات، غذاها ررو مشخص کنید، در اینصورت کوئری بهتری میشه نوشت.
حالا کافیه برای یک محصول خاص کوئری زد

ممنون از وقتی که گذاشتید شایدبهتر باشه توضیحی در مورد جدول سفارشات بدم
چون اسکریپت رو با شرایط جدول تغییر دادم و اجرا کردم خروجی نداد

دو تا جدول درگیر سفارشات هستن
order که اطلاعات خود مشترک رو داره
orderitem اطلاعات سفارش هست و ما با این جدول کار داریم
یعنی محصولات و مخلفات
جدول اردر فیلدی به نام id داره که guid هست و در جدول orderitem کلید خارجی

فیلد های جدول orderitem
[Id]
,[ProductName]
,[Units]
,[OrderId]
,[BaseProductId]
,[ProductCode]

ممنون از وقتی که گذاشتید شایدبهتر باشه توضیحی در مورد جدول سفارشات بدم
چون اسکریپت رو با شرایط جدول تغییر دادم و اجرا کردم خروجی نداد

دو تا جدول درگیر سفارشات هستن
order که اطلاعات خود مشترک رو داره
orderitem اطلاعات سفارش هست و ما با این جدول کار داریم
یعنی محصولات و مخلفات
جدول اردر فیلدی به نام id داره که guid هست و در جدول orderitem کلید خارجی

فیلد های جدول orderitem
[Id]
,[ProductName]
,[Units]
,[OrderId]
,[BaseProductId]
,[ProductCode]

اسکریپت جداول رو قرار بدید

ممنون که وقت میزارید
اسکریپت ایجاد orderitem ونمونه دیتاها ارسال شد

ضمیمه 154621








CREATE TABLE [dbo].[OrderItems](
[Id] [uniqueidentifier] NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[Description] [nvarchar](100) NULL,
[UnitPrice] [bigint] NOT NULL,
[Discount] [bigint] NOT NULL,
[Units] [float] NOT NULL,
[OrderId] [uniqueidentifier] NOT NULL,
[BaseProductId] [int] NOT NULL,
[VAT] [bigint] NOT NULL,
[CatalogTypeId] [int] NOT NULL,
[CatalogTypeName] [nvarchar](100) NOT NULL,
[ProductCode] [nvarchar](50) NULL,
[PackagingCost] [int] NOT NULL,
[VATPercent] [int] NOT NULL)

ProductCode چیه؟ کلیدخارجیه؟ BaseProductIdچطور؟

ProductName نباید توی این جدول باشه همچنین CatalogTypeName

کوئری زیر رو تست کنید

WITH oItems AS (

SELECT 	ROW_NUMBER () OVER ( PARTITION BY order_id ) AS RowNum,

		order_items.item_id,

		order_items.order_id,

		products.product_id,

		products.product_name

FROM order_items

JOIN products on order_items.product_id = products.product_id

)

SELECT 	oItems.product_id as p1_id,

		oItems.product_name p1_name,

		OD.product_id as p2_id, 

		od.product_name as p2_name,

		count(oItems.item_id) AS Count_Of_Order

FROM oItems

JOIN oItems as OD on (oItems.order_id = OD.order_id and oItems.product_id <> OD.product_id and oItems.RowNum < OD.RowNum)

group by oItems.product_id,  OD.product_id

order by oItems.order_id

اسکریپت ضمیمه شده

ممنون
ذهنیت - تون رو از اینکه چند تا جدول بزارید کنار
یک جدول داریم order که اطلاعات خود مشتری رو نگه میداره و یک ای دی به عنوان کلید اصلی داره
یک جدول داریم Orderitem که اطلاعات خرید مشتری رو نگه میداره - ای دی کلید اصلی - Orderid کلید خارجی و کلید دیگه ای نداره
باقی دیتاها مثل baseproduct,productcode,... که در تصویر پاسخ قبلی ارسال شدن در واقع دیتاهایی هستن که از طریق اسکریپت ETL در جدول درج شدن
جدول Orderitem که می بینیم یکی از جداول BI یا warehouse هست و دیتاها بر اساس join جداول دیگر درج شدن
پس ما فقط Orderitem کار داریم و اگر join هم باشه باید با خودش باشه
با این توضیح صورت مسئله رو یکم بیشتر توضیح بدم
مثلا از مجموع 3 تا مشتری که خرید کردن
پتتزا مخلوط خرید کردن هر سه کوکا هم خریدن
پس گام اول برای مشتری چهارم وقتی هنوز توی سایت هست و لاگین نکرده اگر روی پیتزا مخلوط کلیک کرد پایین صفحه قسمت پیشنهادی اول گزینه کوکا رو ببینه


دوم : سه تا مشتری علاوه بر اینکه پیتزا مخلوط و کوکا خریدن - دو تا از اونها سیب زمینی هم سفارش داده
پس برای مشتری چهارم کوکا که نمایش داد بعدی باید سیب زمینی رو نمایش بده


سوم : سه تا مشتری پیتزا مخلوط و کوکا و سیب زمینی خریدن - یکیشون قارچ سوخاری هم سفارش داده
پس مشتری چهارم پیتزا مخلوط کوکا سیب زمینی قارچ سوخاری رو باید به عنوان پیشنهاد ببینه




مشتری میشه OrderId
ایتم هر سفارش میشه Id جدول Orderitem و باقی اطلاعات خرید مشتری

من همون پست اول متوجه شدم چی میخواهی.

اگر نیاز داری روی دیتابیس خودت جواب بده ، اسکریپت جداول رو برام بفرستید در غیر اینصورت پست قبلی (فروشگاه دوچرخه) رو ببینید.


در کدی که ارسال کردم جدول orderitems با خودش join میشه تا متوجه بشیم کدوم محصولات با هم خریده شده اند.
راه ساده تر اینکه، میتوانید فاکتورهایی که مثلا پیتزا سفارش داده اند را سلکت کنید و ببینید چه آیتم های دیگری غیر از پیتزا هم در اون فاکتورها هست و اونها رو پیشنهاد بدید.

select 

    order_items.order_id, 

    order_items.product_id

from order_items

where     order_items.product_id<>@pid AND 

        order_items.order_id in (select order_id 

                                from order_items 

                                where order_items.product_id=@pid)

آیدی مثلا پیتزا رو به پارامتر ارسال کنید.
نام ستونها گویاست، مطابق جدول خودتون اصلاح کنید

ممنون وقت گذاشتید
با همون اسکریپت دوچرخه، اسکریپت رو اصلاح میکنم
موفق باشید
ممنونم ازتون:لبخندساده: