Блог

У меня есть база данных SQLite, и sales таблица выглядит следующим образом,

 | Id | quantity |     dateTime     |
------------------------------------
| 1  | 10       | 2019-12-25 12:55 |
| 2  | 05       | 2019-12-30 12:55 |
| 3  | 25       | 2020-08-23 12:55 |
| 4  | 25       | 2020-08-24 12:55 |
| 5  | 56       | 2020-08-25 12:55 |
| 6  | 25       | 2020-08-26 12:55 |
| 7  | 12       | 2020-08-27 12:55 |
| 8  | 30       | 2020-08-28 12:55 |
| 9  | 40       | 2020-08-29 12:55 |
  

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

Если я передам сегодняшнюю дату и время (2020-08-28 13:55), запрос должен предоставить мне данные текущей недели следующим образом,

 Day        Sold Items (SUM(quantity))
Monday     20
Tuesday    25
Wednesday  10
Thursday   50
Friday     60
Saturday   0 (If the date hasn't come yet I need to get 0)
Sunday     0
  

И такие же, как данные текущего года, когда я передаю текущую дату,

 Month      Sold Items (SUM(quantity))
JAN        20
FEB        25
MAR        10
APR        50
MAY        60
JUN        0 (If the month hasn't come yet I need to get 0)
JUL        0
...        ...
  

Я пробовал с несколькими запросами в SQLite, но не смог получить то, что мне нужно. Вот запросы, которые я пробовал,

 Weekly Data (This one gave me past week data also)
SELECT SUM(quantity) as  quantity, strftime('%w', dateTime) as Day
From sales
Group by strftime('%w', dateTime)

Monthly Data
SELECT SUM(quantity) as  quantity, strftime('%m', dateTime) as Month
From sales
Group by strftime('%m', dateTime)
  

Итак, кто-нибудь может помочь мне достичь этого? Заранее спасибо.

Для итогов текущей недели вам нужен CTE, который возвращает названия дней, и другой, который возвращает понедельник текущей недели.
Вы должны перекрестно объединить эти CTE и слева объединить вашу таблицу для агрегирования:

 with 
  days as (
    select 1 nr, 'Monday' day union all
    select 2, 'Tuesday' union all
    select 3, 'Wednesday' union all
    select 4, 'Thursday' union all
    select 5, 'Friday' union all
    select 6, 'Saturday' union all
    select 7, 'Sunday'
  ),
  weekMonday as (
    select date(
        'now', 
        case when strftime('%w', 'now') <> '1' then '-7 day' else '0 day' end, 
        'weekday 1'
      ) monday
  )
select d.day, 
       coalesce(sum(t.quantity), 0) [Sold Items]
from days d cross join weekMonday wm
left join tablename t
on strftime('%w', t.dateTime)   0 = d.nr % 7
and date(t.dateTime) between wm.monday and date(wm.monday, '6 day')
group by d.nr, d.day
order by d.nr
  

Для итогов текущего года вам нужен CTE, который возвращает названия месяцев, а затем присоединяет таблицу влево для агрегирования:

 with 
  months as (
    select 1 nr, 'JAN' month union all
    select 2 nr, 'FEB' union all
    select 3 nr, 'MAR' union all
    select 4 nr, 'APR' union all
    select 5 nr, 'MAY' union all
    select 6 nr, 'JUN' union all
    select 7 nr, 'JUL' union all
    select 8 nr, 'AUG' union all
    select 9 nr, 'SEP' union all
    select 10 nr, 'OCT' union all
    select 11 nr, 'NOV' union all
    select 12 nr, 'DEC'
  )
select m.month, 
       coalesce(sum(t.quantity), 0) [Sold Items]
from months m
left join tablename t
on strftime('%m', t.dateTime)   0 = m.nr
and date(t.dateTime) between date('now','start of year') and date('now','start of year', '1 year', '-1 day')
group by m.nr, m.month
order by m.nr
  

Вы можете использовать приведенный ниже запрос для получения даты недели, я предполагаю, что everydate имеет одну запись и, следовательно, не группируется, иначе вы можете добавить group by.

Сначала мы получим еженедельный календарь на основе введенной даты (я взял текущую дату), а затем оставим соединение с календарем, чтобы получить необходимую информацию о проданных товарах.

 WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n   1 FROM seq
  WHERE n < DATEDIFF(DAY, (SELECT DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [Week_Start_Date]), (Select DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [Week_End_Date]))
),
CALENDAR(d) AS 
(
  SELECT DATEADD(DAY, n, (SELECT DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) [Week_Start_Date])) FROM seq
)
SELECT coalesce(QUANTITY, 0) sold_items ,DATENAME(WEEKDAY, d) week_day FROM CALENDAR a left outer join Table_WEEKDAY b
on (a.d = convert(date, b.dateTime))
ORDER BY d
OPTION (MAXRECURSION 0);
  

Вы можете попробовать нижеприведенную ДЕМОНСТРАЦИЮ

 select day,coalesce(sum(quantity),0) as quantity
from 
(select 0 as day union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6) as d
left join sales on cast(strftime('%w', dateTime) as int)=day 
group by strftime('%w', dateTime),day
order by day