SQL Сортировка данных по возрастным группам по дате рождения и отметке времени назначения данных.
Я новичок в sql и stackru, поэтому, пожалуйста, проявите милосердие. У меня есть 3 таблицы (описано ниже). Я много искал эту конкретную проблему и не нашел решения. Я аспирант, изучающий поведение и психологию детей по мере их развития на протяжении многих лет. Я делаю это, наблюдая за поведением отдельного ребенка в течение 10 минут и записывая, что они делают каждую минуту. Я делаю это для нескольких детей разного возраста, и я следую за каждым ребенком много раз в течение многих лет. Таким образом, данные являются смешанно-продольными (одни и те же люди представлены несколькими точками данных за годы). Я должен также отметить, что в настоящее время я использую Access.
10-минутный бой называется "следить", а активность в каждую минуту - "сканирование". На протяжении многих лет я вводил эти данные в один лист Excel с датой отслеживания, отметкой времени сканирования, индивидуальным идентификатором, активностью и некоторыми другими вещами. С тех пор я разбил это на две таблицы: таблицу follow_id, которая включает в себя такую информацию, как уникальный идентификационный номер подписки, дата каждого подписки и идентификатор подписчика. Затем я создал вторую таблицу для каждого отдельного сканирования, и каждому сканированию присвоен соответствующий номер follow_id, который является уникальным идентификационным номером для подписки, из которой он получен. Надеюсь, это имеет смысл, я попытался набрать таблицы, но не могу правильно отформатировать. Пожалуйста, прости меня.
Что мне нужно сделать, это отсортировать все сканирования, скажем, для детей в возрасте до 1 года, а затем в возрасте от 1 года и так далее. Это было трудно, потому что почти все мои предметы представлены в нескольких возрастных корзинах. Другими словами, я, возможно, следовал за человеком А в 9 месяцев, затем снова в 16 месяцев. Таким образом, я сделал таблицу дней рождения, в которой есть ID каждого человека и его дни рождения.
Я пробовал кучу запросов и присоединений и еще много чего, но ясно, что я действительно не знаю, что делаю. Я был бы очень признателен, если бы кто-то указал мне правильное направление. Например, я должен начать с объединения? Или все это может работать только с подзапросами? Любое понимание поможет и будет высоко ценится.
tbl_biography
+------------------------------+
| individual_id | birth_date |
+------------------------------+
| AA | 2016-01-01 |
| BB | 2013-01-01 |
| CC | 2014-01-01 |
+------------------------------+
tbl_follow_id
+-------------------------------------------+
| follow_id | individual_id | follow_date |
+-------------------------------------------+
| 0001 | BB | 2013-12-12 |
| 0002 | BB | 2018-01-01 |
| 0003 | BB | 2015-01-01 |
| 0004 | CC | 2016-01-01 |
| 0005 | AA | 2017-01-01 |
+-------------------------------------------+
tbl_scan_id
-follow_id: 0001; 0001; 0001; 0002; 0002; 0002; 0003; 0003; 0003; 0004; 0004; 0004; 0005; 0005; 0005
-scan_id: 00001; 00002; 00003; 00004; 00005; 00006; 00007; 00008; 00009; 00010; 00011; 00012; 00013; 00014; 00015
-timestamp: (I don’t think this really matters here, but each scan would have hh:mm)
-scan: various behaviors such as REST, PLAY, EAT, etc.
Я должен отметить, что вы можете видеть, что индивидуумы AA и BB представлены в наборе данных в 2 отдельных подписках каждый, и у них разные возрасты для каждого подписки. Вопрос в том, как отфильтровать, например, все сканы для детей 0-1, 1-2 лет и т. Д.? Сейчас было бы хорошо объединить всех людей. Но в будущем было бы удивительно, что некоторые люди могли бы делать это, например, по признаку пола, матери и других переменных, которые я также указал в таблице биографии. Спасибо всем заранее.
2 ответа
Во-первых, вам понадобится правильная функция расчета возраста (см. Ниже), поскольку DateDiff возвращает только разницу в календарных годах.
Тогда я бы создал и сохранил запрос следующим образом:
SELECT
Years([birth_date],[follow_date]) AS age,
tbl_biography.individual_id,
tbl_follow.follow_id
FROM
tbl_biography
INNER JOIN
tbl_follow
ON tbl_biography.individual_id = tbl_follow.individual_id
ORDER BY
Years([birth_date],[follow_date]),
tbl_biography.individual_id,
tbl_follow.follow_id;
Это даст такой вывод:
age individual_id follow_id
0 BB 0001
1 AA 0005
2 BB 0003
2 CC 0004
5 BB 0002
Теперь используйте этот запрос и tbl_scan в новом запросе, где вы присоедините их к follow_id. Наконец, добавьте поля из tbl_scan и сортируйте / фильтруйте при необходимости.
Функция:
Public Function Years( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booLinear As Boolean) _
As Integer
' Returns the difference in full years between datDate1 and datDate2.
'
' Calculates correctly for:
' negative differences
' leap years
' dates of 29. February
' date/time values with embedded time values
' negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given datDate1, if datDate2 is decreased step wise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
' 3, 2, 1, 0, 0, -1, -2
' If booLinear is True, the sequence will be:
' 3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2000-11-03. Cactus Data ApS, CPH.
' 2000-12-16. Leap year correction modified to be symmetrical.
' Calculation of intDaysDiff simplified.
' Renamed from YearsDiff() to Years().
' 2000-12-18. Introduced cbytMonthDaysMax.
' 2007-06-22. Version 2. Complete rewrite.
' Check for month end of February performed with DateAdd()
' after idea of Markus G. Fischer.
Dim intDiff As Integer
Dim intSign As Integer
Dim intYears As Integer
' Find difference in calendar years.
intYears = DateDiff("yyyy", datDate1, datDate2)
' For positive resp. negative intervals, check if the second date
' falls before, on, or after the crossing date for a full 12 months period
' while at the same time correcting for February 29. of leap years.
If DateDiff("d", datDate1, datDate2) > 0 Then
intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
intDiff = Abs(intSign < 0)
Else
intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
If intSign <> 0 Then
' Offset negative count of years to continuous sequence if requested.
intDiff = Abs(booLinear)
End If
intDiff = intDiff - Abs(intSign < 0)
End If
' Return count of years as count of full 12 months periods.
Years = intYears - intDiff
End Function
Чтобы рассчитать с месяцами, используйте аналогичную функцию, чтобы сделать это правильно:
Public Function Months( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booLinear As Boolean) _
As Integer
' Returns the difference in full months between datDate1 and datDate2.
'
' Calculates correctly for:
' negative differences
' leap years
' dates of 29. February
' date/time values with embedded time values
' negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
' 3, 2, 1, 0, 0, -1, -2
' If booLinear is True, the sequence will be:
' 3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2010-03-30. Cactus Data ApS, CPH.
Dim intDiff As Integer
Dim intSign As Integer
Dim intMonths As Integer
' Find difference in calendar months.
intMonths = DateDiff("m", datDate1, datDate2)
' For positive resp. negative intervals, check if the second date
' falls before, on, or after the crossing date for a 1 month period
' while at the same time correcting for February 29. of leap years.
If DateDiff("d", datDate1, datDate2) > 0 Then
intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
intDiff = Abs(intSign < 0)
Else
intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
If intSign <> 0 Then
' Offset negative count of months to continuous sequence if requested.
intDiff = Abs(booLinear)
End If
intDiff = intDiff - Abs(intSign < 0)
End If
' Return count of months as count of full 1 month periods.
Months = intMonths - intDiff
End Function
Вы можете рассчитать "Возраст при подписке", используя DateDiff функция, чтобы получить разницу между birth_date и follow_date:
DateDiff("yyyy", tbl_biography.birth_date, tbl_follow_id.follow_date)
Вы можете поместить это выражение в поле в конструкторе запросов.
Вам нужно объединить все три таблицы (потому что вы хотите, чтобы результаты tbl_scan_id и вам нужно tbl_follow_id для follow_date а также tbl_biography для birth_date).
Если в ваших таблицах определены соответствующие отношения, разработчик запросов присоединится к ним, но если нет, вам нужно будет присоединить их вручную (перетаскивая individual_idвместе и follow_idвместе).
Пример SQL ниже:
SELECT
tbl_scan_id.*,
DateDiff("yyyy", tbl_biography.birth_date, tbl_follow_id.follow_date) AS AgeAtFollow,
tbl_follow_id.follow_date,
tbl_biography.individual_id,
tbl_biography.birth_date
FROM
(tbl_follow_id
INNER JOIN tbl_biography
ON tbl_follow_id.individual_id = tbl_biography.individual_id)
INNER JOIN tbl_scan_id
ON tbl_follow_id.follow_id = tbl_scan_id.follow_id;
Затем просто отфильтруйте поле Age At Follow, как вам нужно (или сгруппируйте по нему и т. Д.). Вы также можете переназначить этот запрос и добавить другие поля биографии для фильтрации (пол, мать, демография и т. Д.).