Чистый SQL из postgres в django
У нас есть чистый SQL в postgres, который группирует посещения экземпляров, зарегистрированных в этой таблице, по странам. Мне нужно передать этот чистый запрос в django queryset, чтобы получить тот же результат
Вот чистый SQL-запрос:
SELECT
country,
SUM(difference) AS total_difference
FROM (
SELECT
id,
entry_or_exit_datetime AS entry_time,
LEAD(entry_or_exit_datetime) OVER (ORDER BY entry_or_exit_datetime) AS exit_time,
EXTRACT(EPOCH FROM (LEAD(entry_or_exit_datetime) OVER (ORDER BY entry_or_exit_datetime) - entry_or_exit_datetime)) AS difference,
country
FROM (
SELECT
id,
entry_or_exit_datetime,
country,
LAG(is_joining) OVER (ORDER BY entry_or_exit_datetime) AS prev_is_joining
FROM
public.access_sectionslog
WHERE
date(entry_or_exit_datetime) >= '2024-05-17'
AND date(entry_or_exit_datetime) <= '2024-05-23'
AND section_name = 'landing-page'
AND country IN ('VE', 'CO')
) AS subquery
) AS subquery_with_difference
GROUP BY country;
результат:
Это модель в django:
class SectionsLog(BaseModel):
class SectionNameChoice(models.TextChoices):
GROUPS = "section-groups", _("Groups")
FEED = "section-feed", _("Feed")
NEWS = "section-news", _("News")
LANDING_PAGE = "landing-page", _("Landing Page")
EXTERNALS_SHARING = "external-sharing", _("External Sharing")
LIVESCORE = "section-livescore", _("Livescore")
SALES_PAGE = "sales-page", _("Sales page")
ON_BOARDING = "onboarding", _("On boarding")
user = models.ForeignKey(
User,
on_delete=models.CASCADE,
blank=True,
null=True,
related_name="sections_log",
)
section_name = models.CharField(
max_length=20,
choices=SectionNameChoice.choices,
null=True,
blank=True,
)
is_joining = models.BooleanField(
blank=True,
null=True,
help_text="Enter the Section(True)/Leave the Section(False)",
)
is_anonymous = models.BooleanField(
default=False,
blank=True,
null=True,
help_text="Is True to anybody who enter to the landing page without an user access token.",
)
entry_or_exit_datetime = models.DateTimeField(null=True, blank=True)
user_ip = models.CharField(
max_length=350,
null=True,
blank=True,
)
city = models.CharField(
max_length=220,
null=True,
blank=True,
)
country = models.CharField(
max_length=220,
null=True,
blank=True,
)
latitude = models.CharField(
max_length=90,
null=True,
blank=True,
)
region = models.CharField(
max_length=90,
null=True,
blank=True,
)
timezone = models.CharField(
max_length=90,
null=True,
blank=True,
)
def __str__(self):
direction = "unknow"
user = self.user
if not self.user and self.is_anonymous:
user = f"Anonymous User (IP:{self.user_ip})"
if self.is_joining:
direction = "enter"
else:
direction = "leave"
return f"{user} {direction} {self.section_name} section"
Я пытался сделать это таким образом, но он выдает ошибку.
sportyeah_local_django | File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
sportyeah_local_django | raise dj_exc_value.with_traceback(traceback) from exc_value
sportyeah_local_django | File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 105, in _execute
sportyeah_local_django | return self.cursor.execute(sql, params)
sportyeah_local_django | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sportyeah_local_django | File "/usr/local/lib/python3.11/site-packages/psycopg/cursor.py", line 723, in execute
sportyeah_local_django | raise ex.with_traceback(None)
sportyeah_local_django | django.db.utils.ProgrammingError: window function lead requires an OVER clause
sportyeah_local_django | LINE 1: SELECT "access_sectionslog"."country", SUM((LEAD("access_sec...
Кодекс:
class SectionLogAnalyticsInstaceView(APIView):
@extend_schema(
parameters=[
OpenApiParameter(
"section_name",
OpenApiTypes.STR,
required=False,
description="Ejemplo: external-sharing",
),
OpenApiParameter(
name="start_date",
description="Fecha de inicio del rango de búsqueda (YYYY-MM-DD)",
required=False,
type=OpenApiTypes.DATE,
),
OpenApiParameter(
name="end_date",
description="Fecha de fin del rango de búsqueda (YYYY-MM-DD)",
required=False,
type=OpenApiTypes.DATE,
),
OpenApiParameter(
"country",
OpenApiTypes.STR,
required=False,
description="Ejemplo: ES o VE o tambien CO",
),
],
tags=["analytics"],
)
def get(self, request):
start_date = datetime.strptime('2024-05-17', '%Y-%m-%d').date()
end_date = datetime.strptime('2024-05-23', '%Y-%m-%d').date()
countries = ['VE', 'CO']
# Calcular la duración de la estancia
subquery = SectionsLog.objects.filter(
entry_or_exit_datetime__date__range=(start_date, end_date),
section_name=SectionsLog.SectionNameChoice.LANDING_PAGE,
country__in=countries
).annotate(
prev_is_joining=Window(
expression=Lead('is_joining'),
order_by=F('entry_or_exit_datetime').asc()
)
)
subquery_with_difference = subquery.annotate(
entry_time=F('entry_or_exit_datetime'),
exit_time=Window(
expression=Lead('entry_or_exit_datetime'),
order_by=F('entry_or_exit_datetime').asc()
),
difference=ExpressionWrapper(
RawSQL("EXTRACT(EPOCH FROM (?? - ??))", ['exit_time', 'entry_time']),
output_field=FloatField()
)
)
# Define the final query
final_query = subquery_with_difference.values('country').annotate(
total_difference=Sum('difference')
)
return Response(final_query)
Вы можете использовать .raw(<your SQL query here>), так как он всегда является ответом для сложных запросов, подобных этим. В .raw(<your SQL query here>) вы можете ввести прямой SQL-запрос, как вы указали выше. Но помните, что вам придется менять эти SQL-запросы, т. е. для MySQL или PostgreSQL и т. д., всякий раз, когда вы захотите сменить БД в будущем, если захотите. С другой стороны, запрос ORM не изменится, даже если изменится БД. Дополнительная информация: https://docs.djangoproject.com/en/5.0/topics/db/sql/