enter image description here

उपरोक्त डेटा के लिए, मैं पंक्ति को समूहीकृत करने के आधार पर प्रत्येक खरीद पंक्ति (खरीद = 1) के लिए प्रतिशत मान की गणना करना चाहता हूं।

गणना के लिए शर्त है:

  1. पिछली पंक्तियों का विज़िट_टाइम खरीदारी विज़िट_टाइम से 7 दिनों के भीतर होना चाहिए।
  2. गणना में समान विज़िटर आईडी वाली पंक्तियों पर ही विचार किया जाना चाहिए।

उदाहरण के लिए, प्रतिशत मानों की गणना निम्नानुसार की जानी चाहिए:

  • पंक्ति २ प्रतिशत_वल = पंक्ति १ का मान पंक्ति २ का वैल = ०.२३ x ०.९७ = ०.२२३१
  • पंक्ति ३ प्रतिशत_वल = पंक्ति १ का मान x पंक्ति २ का मान x पंक्ति ३ का मान = ०.२३ x ०.९७ x ०.५५ = ०.१२२७०५
  • पंक्ति ४ प्रतिशत_वल = पंक्ति ४ का मान = ०.११
  • पंक्ति ७ प्रतिशत_वल = पंक्ति का वैल ५ x पंक्ति का वैल ६ x पंक्ति का वैल = ०.५७ x ०.१६ x ०.३४५६ = ०.०३४६५६ (पंक्ति ४ पर विचार नहीं किया जाएगा क्योंकि यह विज़िट_टाइम खरीद पंक्ति की ७ दिनों की सीमा के भीतर नहीं है, अर्थात पंक्ति ७)

मैं SQL सर्वर 2012 का उपयोग कर रहा हूं।

अपेक्षित परिणाम नीचे के समान होगा:

enter image description here

यहां अपेक्षित परिणाम कैसे प्राप्त करें?

परीक्षण डेटा उत्पन्न करने के लिए स्क्रिप्ट:

    CREATE TABLE [#tmp_data]
(
    [visitor]       INT, 
    [visit_id]      INT, 
    [visit_time]    DATETIME, 
    [val]           numeric(4,2),
    [purchase]      BIT
);

INSERT INTO #tmp_data( visitor, visit_id, visit_time,val, purchase )
VALUES( 1, 1001, '2020-01-01 10:00:00', 0.23,0 ), 
( 1, 1002, '2020-01-02 11:00:00', 0.97,1 ), 
( 1, 1003, '2020-01-02 14:00:00', 0.55, 1 ), 
( 2, 2001, '2020-01-01 10:00:00', 0.11, 1 ), 
( 2, 2002, '2020-01-07 11:00:00', 0.57, 0 ), 
( 2, 2003, '2020-01-08 14:00:00', 0.16, 0 ), 
( 2, 2004, '2020-01-11 14:00:00', 0.38, 1 );
2
Samay 28 सितंबर 2020, 10:51

1 उत्तर

सबसे बढ़िया उत्तर

SQL सर्वर में, एक विकल्प लेटरल जॉइन का उपयोग करता है:

select t.*, x.percent_val
from #tmp_data t
cross apply (
    select exp(sum(log(t1.val))) percent_val
    from #tmp_data t1
    where t1.visitor = t.visitor and t1.visit_time > dateadd(day, - 7, t.visit_time) and t1.visit_time <= t.visit_time
) x
where t.purchase = 1

लेटरल जॉइन उसी विज़िटर के लिए पिछले 7 दिनों की विज़िट को पुनर्प्राप्त करता है। फिर, हम मूल्य के कुल उत्पाद की गणना करने के लिए अंकगणित का उपयोग करते हैं (यह तब तक काम करता है जब तक val 0 से बड़ा है)।

DB Fiddle पर डेमो:

visitor | visit_id | visit_time              |  val | purchase | percent_val
------: | -------: | :---------------------- | ---: | :------- | ----------:
      1 |     1002 | 2020-01-02 11:00:00.000 | 0.97 | True     |      0.2231
      1 |     1003 | 2020-01-02 14:00:00.000 | 0.55 | True     |    0.122705
      2 |     2001 | 2020-01-01 10:00:00.000 | 0.11 | True     |        0.11
      2 |     2004 | 2020-01-11 14:00:00.000 | 0.38 | True     |    0.034656

यदि आप 0 मानों को भी संभालना चाहते हैं, तो आप suquery के select खंड को बदल सकते हैं:

select case when min(val) = 0 
    then 0 
    else exp(sum(log(case when val > 0 then t1.val end))) 
end percent_val
4
GMB 28 सितंबर 2020, 15:37